multiple cte's between insert into statements

gchavez58
gchavez58 used Ask the Experts™
on
I need to use several cte's for an inset into then a couple more cte's for another insert into, it completes the first insert into and errors on the second. I then stacked all the cte's then my insert into statements, the first insert worked the second threw an error. So is there not a way to termianate a chain of cte's then sql code like insert into then more cte's?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
Any CTEs only apply to that single statement.  If you need multiple CTEs for multiple INSERTs, you will have to code all the CTEs on all the INSERTs.
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Would help if you posted your T-SQL code, but Scott answered your question in that any number of daisy-chained CTE's will still only affect a single query.

Author

Commented:
Wrong answer you cannot have multiple cte's then a sql statement then more cte's with yet another sql statement you can only have one sql statement with your cte's not matter how many cte's you may need you can only have one sql statement against the cte's a flaw in sql. So you cannot have cte's then an insert then more cte's with another insert MS does not allow this.
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
Of course you can have multiple INSERTs each with their own set of CTEs.  As I stated, CTEs apply only to the one statement, but you can repeat the same CTE on every statement in your batch if you want to.

;WITH CTE1 AS ( SELECT col1 FROM table1 ),
 CTE2 AS (SELECT col2 FROM table2 ),
 CTE3 AS (SELECT col3 FROM table3)
INSERT INTO table9
SELECT
FROM CTE1, CTE2, CTE3

;WITH CTE1 AS ( SELECT col1 FROM table1 ),
 CTE2 AS (SELECT col2 FROM table2 ),
 CTE4 AS (SELECT col3 FROM table4)
INSERT INTO table10
SELECT
FROM CTE1, CTE2, CTE4
    --CTE1 and CTE2 can only be referenced because they are defined for this statement also;
    --CTE3 cannot be referenced here, since it's from an earlier statement

etc.

Author

Commented:
Yes you can do that BUT you cannot do this, you cannot have more thatn one sql statement with your cte's of course you can have many cte's then a sql statement such as insert into but you cannot have more than one insert into with seperate cte's it is not allowed your example only has ONE insert into statement my original post was just this. i cannot find a way around this I tried to term the first chain of cte's then anoth chain and you just cannot do it.


WITH CTE1 AS ( SELECT col1 FROM table1 ),
 CTE2 AS (SELECT col2 FROM table2 ),
 CTE3 AS (SELECT col3 FROM table3)
INSERT INTO table9
SELECT
FROM CTE1, CTE2, CTE3

;WITH CTE1 AS ( SELECT col1 FROM table1 ),
 CTE2 AS (SELECT col2 FROM table2 ),
 CTE4 AS (SELECT col3 FROM table4)
INSERT INTO table10
SELECT
FROM CTE1, CTE2, CTE4
    --CTE1 and CTE2 can only be referenced because they are defined for this statement also;
    --CTE3 cannot be referenced here, since it's from an earlier statement


WITH CTE5 AS ( SELECT col1 FROM table1 ),
 CTE6 AS (SELECT col2 FROM table2 ),
 CTE7 AS (SELECT col3 FROM table3)
INSERT INTO table10
SELECT
FROM CTE5, CTE6, CTE7

WITH CTE5 AS ( SELECT col1 FROM table1 ),
 CTE6 AS (SELECT col2 FROM table2 ),
 CTE7 AS (SELECT col3 FROM table3)
INSERT INTO table10
SELECT
FROM CTE5, CTE6, CTE7

SELECT
FROM CTE1, CTE2, CTE4
    --CTE1 and CTE2 can only be referenced because they are defined for this statement also;
    --CTE3 cannot be referenced here, since it's from an earlier statement
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
I stated that as my very first comment:
Any CTEs only apply to that single statement.
That is, the single statement that immediately follows those CTEs.  That's *it*.  They're not intended to be reference-able by any other statement, and they simply cannot be.
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
>i cannot find a way around this I tried to term the first chain of cte's then anoth chain and you just cannot do it.
Correct, this is working as designed in that it can't be done with CTE's, so you'll likely need to either re-state your CTE's for each insert statement, or use temp tables.

See MSDN's CTE page, first line ... This is derived from a simple query and defined within the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement.
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
Perhaps you need to do 2 things:

1. consider providing us with the existing code to look at
2. consider using temporary tables so that they persist across several insert statements

but until we know more all we an do is provide generic advice:

which is that the scope of CTE's is limited to ONE statement
e.g.
;with X as (...),
with Y as (...),
with Z as (...)
insert ...
;

& at this point cte's X  & Z are now completely dead
I cannot refer to them again (unless I rebuild them)
G GodwinDatabase Administrator

Commented:
If I may:

All you need to do is select from the CTEs into temp tables. Then you can do your final insert into the permanent table with the results of all CTEs in one statement.

If the results are small enough, I'd even send them to table variables.

--
GDG

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial