How to write "WITH" results to temp table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

How to write "WITH" results to temp table

Author

Topic

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2011-08-04 : 09:26:58

Anyone know how can I write the results of the following WITH statement to a temporary table?

Thanks
Q

WITH
EXPLODED AS
(
SELECT A, B FROM TABLENAME UNION ALL
SELECT A, B - 1 FROM EXPLODED WHERE B > 1
)
SELECT *, ROW_NUMBER() OVER (ORDER BY A, B) AS unique_ref FROM EXPLODED
ORDER BY A, B
OPTION (MAXRECURSION 0);

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-04 : 09:32:11

[code]
WITH
EXPLODED AS
(
SELECT A, B FROM TABLENAME UNION ALL
SELECT A, B - 1 FROM EXPLODED WHERE B > 1
)
INSERT INTO #Temptable
SELECT *, ROW_NUMBER() OVER (ORDER BY A, B) AS unique_ref FROM EXPLODED
ORDER BY A, B
OPTION (MAXRECURSION 0);
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-08-04 : 09:32:25

[code]WITH
EXPLODED AS
(
SELECT A, B FROM TABLENAME UNION ALL
SELECT A, B - 1 FROM EXPLODED WHERE B > 1
)
SELECT *, ROW_NUMBER() OVER (ORDER BY A, B) AS unique_ref
INTO #TempTable FROM EXPLODED
ORDER BY A, B
OPTION (MAXRECURSION 0);[/code]This will create the temp table and insert the data into that table.

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2011-08-04 : 09:33:40

Man that was fast, you all RULE!

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2011-08-04 : 10:05:08

Well, I am almost there.... Can you guys please tell me why I am getting "incorrect syntact, expecting (" after the first "Exploded" reference? I am also getting invalide column names after the 2nd 'A' and 'B' reference....
The syntax looks fine to me... I should state that I have this code as part of a Stored Procedure.

-- Create temp table that will be used to house "exploded" records.
create table #ExplodedTable (A nvarchar(3), B int, UNIQUE_REF int)

with
EXPLODED as (
select A, B from TABLENAME union ALL
select A, B - 1 from EXPLODED where B > 1
)
insert into #ExplodedTable
select *, ROW_NUMBER() over (order by A, B) as unique_ref from EXPLODED
order by A, B
-- set maxrecursion variable to 0 in order to correct error related to default 100 value
option (MAXRECURSION 0);

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-08-04 : 10:37:09

Insert a semi-colon after the create table statement.

-- Create temp table that will be used to house "exploded" records.
create table #ExplodedTable (A nvarchar(3), B int, UNIQUE_REF int); 

with
EXPLODED as (
select A, B from TABLENAME union ALL
select A, B - 1 from EXPLODED where B > 1
)
insert into #ExplodedTable
select *, ROW_NUMBER() over (order by A, B) as unique_ref from EXPLODED
order by A, B
-- set maxrecursion variable to 0 in order to correct error related to default 100 value
option (MAXRECURSION 0);

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2011-08-04 : 10:44:03

If only they all could be that easy... Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources