Combining INSERT INTO and WITH/CTE

user_91396 0
Viewed 254783

I have a very complex CTE and I would like to insert the result into a physical table.

Is the following valid?

INSERT INTO dbo.prf_BatchItemAdditionalAPartyNos 
(
    BatchID,
    AccountNo,
    APartyNo,
    SourceRowID
)       
WITH tab (
  -- some query
)    
SELECT * FROM tab

I am thinking of using a function to create this CTE which will allow me to reuse. Any thoughts?

insert common-table-expression tsql
4 Answers

Late to the party here, but for my purposes I wanted to be able to run the code the user inputted and store in a temp table. Using oracle no such issues.. the insert is at the start of the statement before the with clause.

For this to work in sql server, the following worked:

INSERT into #stagetable execute (@InputSql)

(so the select statement @inputsql can start as a with clause).

user_157089030
Related