How do you retrieve the new keys from an INSERT IN

问题:

Is there a way to retrieve all the keys of the newly inserted records when using an INSERT INTO ... SELECT FROM query?

回答1:

DECLARE @MyVar TABLE ( ID int )

INSERT  INTO dbo.TargetTable
    OUTPUT  INSERTED.ID INTO @MyVar
SELECT * FROM    dbo.SourceTable

SELECT  * FROM    @MyVar

回答2:

Use the OUTPUT clause to capture them (SQL Server 2005 and up).

回答3:

Some databases support the INSERT INTO ... SELECT ... RETURNING ... syntax. Since you are using TSQL, I believe the syntax for that is:

INSERT INTO table (fields...)
OUTPUT outputfields...
SELECT ...

There's a PDF on the issue: Returning.pdf