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?
gchavez58Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
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 HornMicrosoft SQL Server Data DudeCommented:
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.
gchavez58Author 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.
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Scott PletcherSenior DBACommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gchavez58Author 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 DBACommented:
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 HornMicrosoft SQL Server Data DudeCommented:
>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 AdvisorCommented:
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 AdministratorCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.