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

Answer 1

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