4

I have a CTE and query this one

;With CTE_Table as (SELECT ...)
Select * from CTE_Table

Now I try to save this result into a table variable or temporary table. If I try

Declare @Table table
(...)
INSERT INTO @Table (...)
HER I PUT THE CODE ABOVE

I get an incorrect syntax error around the WITH statement. I guess the Insert Into command expects a Select statement instead of a ;WITH and the same goes for Create Table. I was looking for solutions but all I found did not involve the Select Statement after the ;With. How do I get the output shown above into a temporary table or table variable?

I use SQL Server 2014.

ruedi
  • 5,365
  • 15
  • 52
  • 88
  • The keyword to start a CTE is `with` not `;with`. –  Sep 02 '15 at 10:43
  • @a_horse_with_no_name Really? – paparazzo Sep 02 '15 at 11:21
  • @Frisbee: yes, see Mikael's answer for a correct usage of the statement _termination_ character –  Sep 02 '15 at 11:26
  • @a_horse_with_no_name Terminator is not line dependent. You can move it to the "with" line. ;with is not keyword. It is a terminator followed by a keyword. – paparazzo Sep 02 '15 at 11:30
  • @Frisbee: I know that. But the constant pattern of writing `;with` apparently makes a lot of people think that a CTE is started by using `;with` see e.g. questions like this: http://stackoverflow.com/q/11491240/330315 or this http://stackoverflow.com/q/26670414/330315 and the way this question is written kind give me the impression that ruedi also thinks that a CTE needs to start with `;with` –  Sep 02 '15 at 11:38
  • the answer to this question (http://stackoverflow.com/questions/6938060/common-table-expression-why-semicolon) contains all information about it . There you can find the answer why I used the ; before with. Thanks for the discussion though I should not have used that in this example, it is confusing. – ruedi Sep 02 '15 at 11:54
  • @a_horse_with_no_name And your inference that ;with is a keyword does not add clarity – paparazzo Sep 02 '15 at 11:58

2 Answers2

10

You add the insert statement to the main query right after the CTE declarations.

declare @T table(ID int);

with C(ID) as
(
  select 1 union all
  select 2
)
insert into @T(ID)
select ID
from C;

select ID
from @T;
Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
0
DECLARE @t table(
Name nvarchar(MAX),
Id bigint
)

;WITH CTE as (
    SELECT 'Name' as Name , 1 as Id
)
INSERT INTO @t(Name,Id)
SELECT Name,Id FROM CTE


SELECT * FROM @t
rollo
  • 305
  • 3
  • 14