SELECT values of an INSERT INTO statement

Question

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?

score 2 · Answer 1 · answered Oct 07 '15 at 03:40

Use a batch statement and store the intermediate results in a table variable:

DECLARE @intermediate TABLE (
    col1 type,
    col2 type,
    col3 type
);
INSERT INTO @intermediate VALUES ( ... );
INSERT INTO destinationTable SELECT * FROM @intermediate;
SELECT @intermediate;

If using this from code you can have all of this in a single command-text string.

score 2 · Accepted Answer · answered Oct 07 '15 at 03:48

2

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

answered Oct 07 '15 at 03:48

Code Different

90,614
16
144
163

score 0 · Answer 3 · answered Oct 07 '15 at 03:43

0

Have you tried something like this:

INSERT INTO Table1 (
SELECT Phrase, Notes, GetDate, 1
FROM Table2
UNION
SELECT Phrase, 'This is an automatic note by the system', GETDATE(), 1
UNION
)

answered Oct 07 '15 at 03:43

Jim

14,952
15
80
167

SELECT values of an INSERT INTO statement

3 Answers3