Combining INSERT INTO and WITH/CTE

172
votes

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?

4
tsqlinsertcommon-table-expression

4 Answers

299
votes

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)

27
votes

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.

18
votes

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.

0
votes

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).