August 6, 2009 at 3:53 am
Hi all,
I am working with CTE I got problem like
;with CTE1(col1,col2)
as
(
Select Col1,col2 From table 1
)
INSERT INTO table2
Select * from CTE1
Insert Into table3
Select * from CTE1
It gave error like
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'CTE1'
pls help me.
August 6, 2009 at 5:26 am
I think you will have to declare the CTE for each insert as below, I don't think that the CTE can be shared between two seperate select statements.
WITH CTE1(col1,col2)
AS
(SELECT col1,col2 FROM table1)
INSERT INTO table2
SELECT * FROM CTE1
;
WITH CTE1(col1,col2)
AS
(SELECT col1,col2 FROM table1)
INSERT INTO table3
SELECT * FROM CTE1
August 6, 2009 at 6:24 am
A CTE can be used multiple times within a single statement, but it can't be shared across statements. If you need to use it twice, you'll either have to create it twice, or load the data from the CTE into a temp table.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
August 9, 2009 at 10:54 pm
Thank'Q for your reply
August 9, 2009 at 10:54 pm
Thank'Q for your reply:-).
August 10, 2009 at 1:04 am
Or use the new OUTPUT operator?
;with CTE1(col1,col2)
as
(
Select Col1,col2 From table 1
)
INSERT INTO table2
output inserted.*
into table3
Select * from CTE1
N 56°04'39.16"
E 12°55'05.25"
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply