INSERT INTO + CTE SELECT

Today I was doing a development where I wanted to insert a output of a CTE to a temp variable,

normal INSERT T-SQL syntax wont work as CTE needs a semi-colon before the start and will give the below error,

 

Msg 336, Level 15, State 1, Line 11

Incorrect syntax near 'grp_opbx'. If this is intended to be a common table expression, you need to explicitly terminate the previous statement with a semi-colon.

 

Here is the right syntax,

 

DECLARE @tmptbl TABLE (tmp_col1 int,tmp_col2 int);

WITH Testcte( col1, col2) AS

(

SELECT a.samplecol,b.samplecol2 from testdb1.dbo.testfunction(arg1) AS i

JOIN

testdb2.dbo.tbl123 AS b ON a.check1 = b.check2

)

INSERT INTO @tmptbl

SELECT col1, col2 from Testcte

--Check the result

SELECT * from @tmptbl