Combining INSERT INTO and WITH/CTE

by

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)

More Related Contents:

  1. SQl CASE Statement
  2. Inserting multiple rows in a single SQL query? [duplicate]
  3. Insert multiple rows WITHOUT repeating the “INSERT INTO …” part of the statement?
  4. Convert multiple rows into one with comma as separator [duplicate]
  5. How can I use optional parameters in a T-SQL stored procedure?
  6. T-SQL Decimal Division Accuracy
  7. Selecting a Record With MAX Value
  8. How to convert Seconds to HH:MM:SS using T-SQL
  9. T-SQL: Selecting rows to delete via joins
  10. Difference between CTE and SubQuery?
  11. Common Table Expression, why semicolon?
  12. What is a “batch”, and why is GO used?
  13. Getting the minimum of two values in SQL
  14. Is there a performance difference between CTE , Sub-Query, Temporary Table or Table Variable?
  15. T-SQL loop over query results
  16. How to insert default values in SQL table?
  17. How to Round a Time in T-SQL
  18. T-SQL CASE Clause: How to specify WHEN NULL
  19. Return a value if no rows are found in Microsoft tSQL
  20. Recursive sum in tree structure
  21. Check if a varchar is a number (TSQL)
  22. Using Aliases in Where Clause or an Alternative Option?
  23. SQL Recursive CTE: Finding objects linked by property
  24. Extract the first word of a string in a SQL Server query
  25. Search for one value in any column of any table inside a database
  26. Check if a varchar is a number (T-SQL)
  27. Truncate Datetime to Second (Remove Milliseconds) in T-SQL
  28. Convert unix epoch timestamp to TSQL datetime
  29. T-SQL Between Dates Confusion
  30. Poor Man’s SQL Pivot. List Questions as Columns and Answers per User in one row

Leave a Comment