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?
Microsoft SQL Server 2008

Avatar of undefined
Last Comment
G Godwin

8/22/2022 - Mon
Scott Pletcher

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 Horn

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.
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.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER CERTIFIED SOLUTION
Scott Pletcher

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
gchavez58

ASKER
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 Pletcher

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 Horn

>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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
PortletPaul

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 Godwin

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