CODEWITHSUNDEEP
sqlt-sqlcommon-table-expressionsql-server-2014table-variable
ruedi
ruedi

Reputation: 5555

How to save a queried CTE output into a temporary table or table variable

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.

Upvotes: 4

Views: 12970

Answers (2)

rollo
rollo

Reputation: 305

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

Upvotes: 0

Mikael Eriksson
Mikael Eriksson

Reputation: 138980

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;

Upvotes: 10

Related Questions