Combining INSERT INTO and WITH/CTE

Question

Hi there

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

INSERT INTO dbo.prf_BatchItemAdditionalAPartyNos (  BatchID,                                                        AccountNo,
APartyNo,
SourceRowID)    

WITH tab (
  bla bla
)

SELECT * FROM tab

I am thinking of using a function to create this CTE stuff and reuse in here? Any thought?

Thanks

Answer 1

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.

Answer 2

You need to put the CTE first and then combine the INSERT INTO with your select statement, like this:

WITH tab (
    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).