Search code examples
sql-server-2012select-into

SELECT values of an INSERT INTO statement

I need to select a few columns from a table1. I need to insert only one of these columns as well as some arbitrary hard coded data and insert it into table2 while also getting the original select statement back.

Basically I would like to get the results of my INSERT INTO statement instead of the "(1 row(s) affected)" that I get in SSMS.

Is there a way to do this?

Here is a SQLFiddle: http://sqlfiddle.com/#!3/e9beb/3

Those records will insert just fine. However, I want the results of my SELECT statement to come back to me so that I can do it all at once without multiple reads or trips. Is this possible?

Solution

  • You can use the OUTPUT clause:

    INSERT INTO Table2
OUTPUT inserted.*
SELECT Phrase, 'This is an automatic note by the system', GETDATE(), 1
FROM Table1