Combining INSERT INTO and WITH CTE

Question

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

User · Accepted Answer

You need to put the CTE first and then combine the INSERT INTO with your select statement. Also, the "AS" keyword following the CTE's name is not optional:

WITH tab AS (
    bla bla
)
INSERT INTO dbo.prf_BatchItemAdditionalAPartyNos (
BatchID,
AccountNo,
APartyNo,
SourceRowID
)  
SELECT * FROM tab

Please note that the code assumes that the CTE will return exactly four fields and that those fields are matching in order and type with those specified in the INSERT statement. If that is not the case, just replace the "SELECT *" with a specific select of the fields that you require.

As for your question on using a function, I would say "it depends". If you are putting the data in a table just because of performance reasons, and the speed is acceptable when using it through a function, then I'd consider function to be an option. On the other hand, if you need to use the result of the CTE in several different queries, and speed is already an issue, I'd go for a table (either regular, or temp).

WITH common_table_expression (Transact-SQL)

User · Answer

Yep   WITH tab     bla bla    INSERT INTO dbo prf BatchItemAdditionalAPartyNos    BatchID                                                         AccountNo  APartyNo  SourceRowID       SELECT   FROM tab   Note that this is for SQL Server  which supports multiple CTEs   WITH x AS     y AS    INSERT INTO z  a  b  c  SELECT a  b  c FROM y   Teradata allows only one CTE and the syntax is as your example

User · Answer

The WITH clause for Common Table Expressions go at the top   Wrapping every insert in a CTE has the benefit of visually segregating the query logic from the column mapping   Spot the mistake   WITH  INSERT  AS     SELECT      BatchID         blah      APartyNo        blahblah      SourceRowID     blahblahblah   FROM Table1 AS t1   INSERT Table2         BatchID    SourceRowID    APartyNo   SELECT  BatchID    APartyNo    SourceRowID     FROM  INSERT    Same mistake   INSERT Table2      BatchID     SourceRowID     APartyNo    SELECT    BatchID         blah    APartyNo        blahblah    SourceRowID     blahblahblah FROM Table1 AS t1   A few lines of boilerplate make it extremely easy to verify the code inserts the right number of columns in the right order  even with a very large number of columns  Your future self will thank you later

[tsql] Combining INSERT INTO and WITH/CTE

Examples related to tsql

Examples related to insert

Examples related to common-table-expression