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 · 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

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

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

[tsql] Combining INSERT INTO and WITH/CTE

Examples related to tsql

Examples related to insert

Examples related to common-table-expression