Wednesday 22 August 2012

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

No comments:

Post a Comment