How to Retrieve The Identities of Rows That Were Inserted Through Insert...Select

How can I retrieve the identities of rows that were inserted through insert...select?

You can get this information using the OUTPUT clause.

You can output your information to a temp target table or view.

Here's an example:

DECLARE @InsertedIDs TABLE (ID bigint)
INSERT into DestTable (col1, col2, col3, col4)
OUTPUT INSERTED.ID INTO @InsertedIDs
SELECT col1, col2, col3, col4 FROM SourceTable

You can then query the table InsertedIDs for your inserted IDs.

How to get the identity of an inserted row?

• @@IDENTITY returns the last identity value generated for any table in the current session, across all scopes. You need to be careful here, since it's across scopes. You could get a value from a trigger, instead of your current statement.

• SCOPE_IDENTITY() returns the last identity value generated for any table in the current session and the current scope. Generally what you want to use.

• IDENT_CURRENT('tableName') returns the last identity value generated for a specific table in any session and any scope. This lets you specify which table you want the value from, in case the two above aren't quite what you need (very rare). Also, as @Guy Starbuck mentioned, "You could use this if you want to get the current IDENTITY value for a table that you have not inserted a record into."

• The OUTPUT clause of the INSERT statement will let you access every row that was inserted via that statement. Since it's scoped to the specific statement, it's more straightforward than the other functions above. However, it's a little more verbose (you'll need to insert into a table variable/temp table and then query that) and it gives results even in an error scenario where the statement is rolled back. That said, if your query uses a parallel execution plan, this is the only guaranteed method for getting the identity (short of turning off parallelism). However, it is executed before triggers and cannot be used to return trigger-generated values.

Is there a way to retrieve inserted identity as well as some values from the query in an INSERT SELECT?

With INSERT ... OUTPUT ... SELECT ... you can't output columns that are not in the target table. You can try MERGE instead:

MERGE INTO A as tgt
    USING   (SELECT Id, SomeValue FROM A WHERE <your conditions>) AS src
    ON      0 = 1
    WHEN NOT MATCHED THEN
            INSERT (SomeValue)
            VALUES (src.SomeValue)
            OUTPUT (inserted.Id, src.Id) -- this is your new Id / old Id mapping
            INTO    @inserted
;

Retrieve original and new identities mapping from SELECT INSERT statement using OUTPUT clause

It can be achieved using MERGE INTO and OUTPUT:

MERGE INTO MyTable AS tgt
USING MyTable AS src ON 1=0 --Never match
WHEN NOT MATCHED THEN
INSERT (Name)
VALUES (src.Name)
OUTPUT
    src.Id,
    inserted.Id
INTO @idsMap;

Inserting the Identity of another Insert from Select?

You have, at least, two options:

1) The OUTPUT...INTO target_table clause (SQL2005+)

2) Or you could write composable DML(SQL2008+).

Example:

DECLARE @Table2 TABLE(
    ID  INT IDENTITY PRIMARY KEY, --IDENTITY 
    xID INT NOT NULL,
    NAME VARCHAR(25) NOT NULL
);

DECLARE @Table3 TABLE(
    ID  INT PRIMARY KEY, --No IDENTITY 
    xID INT NOT NULL,
    NAME VARCHAR(25) NOT NULL
);

--First solution: OUTPUT ... INTO
INSERT  INTO @Table2 (xID, NAME)
OUTPUT  inserted.xID, inserted.NAME, inserted.ID INTO @Table3(xID, NAME, ID)
SELECT  t.Col1, t.Col2
FROM    (SELECT 11,'A' UNION ALL SELECT 22,'B' UNION ALL SELECT 33,'C') AS t(Col1,Col2);

--Second solution: composable DML
INSERT  INTO @Table3(xID, NAME, ID)
SELECT  src.xID, src.NAME, src.ID
FROM
(
        INSERT  INTO @Table2 (xID, NAME)
        OUTPUT  inserted.xID, inserted.NAME, inserted.ID 
        SELECT  t.Col1, t.Col2
        FROM    (VALUES(44,'D'),(55,'E'),(66,'F')) AS t(Col1,Col2)
) src

SELECT * FROM @Table2 
SELECT * FROM @Table3

How do I get the ID of multiple inserted rows in MySQL?

1. You get it via SELECT LAST_INSERT_ID(); or via having your framework/MySQL library (in whatever language) call mysql_insert_id().

2. That won't work. There you have to query the IDs after inserting.

---

Related Topics