0

I am writing a query in MSSQL. I am writing a huge logic in CTE, this logic has to be used to insert data into multiple tables. I dont want to write this CTE for every table, want it to be generic for every table.

Example:

with CTE as (Select * from table)
select * into table3 from table1 join cte
select * into table4 from table2 join cte

is it possible to achieve it

I tried to write the insert statement and tried to use the same CTE in generic way

Thom A
  • 88,727
  • 11
  • 45
  • 75
db2db2
  • 1
  • You could instead dump the results of {cte} into a {#temp table}. If you only need to insert the rows into _two_ tables, you could perhaps use `WITH cte AS () INSERT dbo.table1 OUTPUT inserted.cols INTO dbo.table2 SELECT FROM cte;`. – Stuck at 1337 Aug 11 '23 at 16:56

1 Answers1

0

A CTE must be complied immediately before using it in your subsequent SQL to do the insert. Have you considered creating a VIEW instead of a CTE? That way you could re-use the logic over and over again without having to repeat the CTE each time.

Josh
  • 1,493
  • 1
  • 13
  • 24