TSQL INSERT INTO SELECT - display rows

Question

Is it possible to display which rows I did inserted via this query:

INSERT INTO dbo.Table (Col1, Col2, Col2)
    (SELECT
        Col1,
        Col2,
       'Something modified',
    FROM dbo.Table
    WHERE Col10 = 66)

It is important to obtain whole new row with it's PrimaryKey value, etc., not only Col1, Col2, Col3

[`OUTPUT` clause](http://msdn.microsoft.com/en-us/library/ms177564.aspx) — Damien_The_Unbeliever, May 28 '14 at 07:20
possible duplicate of [How to get last inserted id?](http://stackoverflow.com/questions/5228780/how-to-get-last-inserted-id) — Ocaso Protal, May 28 '14 at 07:23

score 7 · Accepted Answer · edited Mar 03 '16 at 12:40

7

The OUTPUT clause is your friend now:

INSERT INTO steam.DocumentGeographies (Col1, Col2, Col3)
OUTPUT inserted.*
    (SELECT
        Col1,
        Col2,
       'Something modified',
    FROM dbo.Table
    WHERE Col10 = 66)

You can insert the result of the OUTPUT by specifying the destination table with the INTO clause or by wrapping the query as a subquery:

INTO clause

This is useful, when you want to insert the same data into two tables. You can always list the required fileds in the OUTPUT clause (inserted.Col1, inserted.Col2)

INSERT INTO steam.DocumentGeographies (Col1, Col2, Col3)
    OUTPUT inserted.* INTO DestinationTableName(Col1, Col2, Col3)
        (SELECT
            Col1,
            Col2,
           'Something modified',
        FROM dbo.Table
        WHERE Col10 = 66)

SUBQUERY

This is useful, when you want to join the OUTPUT to another tables or you want to make calculations (like summing or counting values) and insert those results into another table.

INSERT INTO DestinationTableName
(Col1, Col2, Col3)
SELECT Col1, Col2, Col3 FROM (
    INSERT INTO steam.DocumentGeographies (Col1, Col2, Col3)
    OUTPUT inserted.*
        (SELECT
            Col1,
            Col2,
           'Something modified',
        FROM dbo.Table
        WHERE Col10 = 66)
) TMP

edited Mar 03 '16 at 12:40

Bakudan

19,134
9
53
73

answered May 28 '14 at 07:23

Pred

8,789
3
26
46

@Pred Nice piece of code and new to me. Is there any way to assign inserted value (mainly auto increment) to a variable? – TechDo May 28 '14 at 07:56
Is it possible to save INSERTED.* to separate temporary table? – Fka May 28 '14 at 07:59
Yes, it is! Wrap this query as a subquery and insert its result into the temporary table. – Pred May 28 '14 at 08:20
@TechDo: The OUTPUT clause will 'return' a table, not a single value. You can insert the result into a table variable (`DECLARE @TableVar TABLE (ColumnDefinitions)`) and you can do additional processing on that table (like on any tables) – Pred May 28 '14 at 08:30
@Pred Thank you... Helped a lot and a new concept. – TechDo May 28 '14 at 08:58
@Pred very, very helpful! – Fka May 28 '14 at 11:24

TSQL INSERT INTO SELECT - display rows

1 Answers1

Linked