CTE problem

Question

CTE problem

venki_1276

Mr or Mrs. 500

Points: 512
More actions
August 6, 2009 at 3:53 am

#137115

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.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Nick_UK SSCommitted Points: 1909 More actions · Answer 1

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

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 2

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 Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning