Insert Into… Merge… Select (SQL Server)

by

The trick is to populate the table with the MERGE statement instead of an INSERT...SELECT. That allowes you to use values from both inserted and source data in the output clause:

MERGE INTO Table3 USING
(
    SELECT null as col2, 
           110 as col3, 
           Table1.ID as col4, 
           Table2.Column2 as col5,
           Table2.Id as col6
    FROM Table1
    JOIN Table1Table2Link on Table1.ID=Table1Table2Link.Column1
    JOIN Table2 on Table1Table2Link.Column2=Table2.ID
) AS s ON 1 = 0 -- Always not matched
WHEN NOT MATCHED THEN
INSERT (Column2, Column3, Column4, Column5)
VALUES (s.col2, s.col3, s.col4, s.col5)
OUTPUT Inserted.ID, s.col6
INTO @MyTableVar (insertedId, Table2Id);

More Related Contents:

  1. How to cast the DateTime to Time
  2. Row Offset in SQL Server
  3. Reset identity seed after deleting records in SQL Server
  4. How to avoid the “divide by zero” error in SQL?
  5. SQL Server recursive self join
  6. Multiple INSERT statements vs. single INSERT with multiple VALUES
  7. Generate a resultset of incrementing dates in TSQL
  8. How to simulate UNPIVOT in Access?
  9. Find a string by searching all tables in SQL Server
  10. Real life example, when to use OUTER / CROSS APPLY in SQL
  11. How to get script of SQL Server data? [duplicate]
  12. Better techniques for trimming leading zeros in SQL Server?
  13. SSIS Package not wanting to fetch metadata of temporary table
  14. How to store historical records in a history table in SQL Server
  15. SQL GROUP BY CASE statement with aggregate function
  16. How can I include null values in a MIN or MAX?
  17. separate comma separated values and store in table in sql server
  18. How to export image field to file?
  19. Move SQL data from one table to another
  20. Get the last day of the month in SQL
  21. INSERT VALUES WHERE NOT EXISTS
  22. Nested select statement in SQL Server
  23. Cannot create an instance of OLE DB provider Microsoft.Jet.OLEDB.4.0 for linked server null
  24. Inner Joining three tables
  25. SQL take just the numeric values from a varchar
  26. SQL Server 2005 – Export table programmatically (run a .sql file to rebuild it)
  27. What’s the correct name for an “association table” (a many-to-many relationship) [closed]
  28. Convert bigint to datetime
  29. SQL SERVER DATETIME FORMAT
  30. Insert all values of a table into another table in SQL

Leave a Comment