Link to home
Start Free TrialLog in
Avatar of gchavez58
gchavez58

asked on

multiple cte's between insert into statements

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?
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

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.
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.
Avatar of gchavez58
gchavez58

ASKER

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.
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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.
>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.
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)
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