Home How to retrieve SCOPE_IDENTITY of all inserts done in INSERT INTO [table] SELECT [col1, …]

Questions

How to retrieve SCOPE_IDENTITY of all inserts done in INSERT INTO [table] SELECT [col1, …]

July 17, 2022

Suppose I have a temp table with some cols one of which I have dedicated to identity column of the inserted Invoice and the others for inserting Invoice data itself. Like the following table :

CREATE TABLE #InvoiceItems
(
    RowNumber INT, -- Used for inserting new invoice
    SaleID INT,    -- Used for inserting new invoice
    BuyerID INT,   -- Used for inserting new invoice
    InvoiceID INT  -- Intended for PK of the invoice added after inserting it
);

I use something like the following for inserting data into Invoice table

INSERT INTO [Invoice] 
    SELECT [col1, ...] 
    FROM #InvoiceItems

How can I achieve to fill the InvoiceID column while inserting table data into Invoice table using temp table? I know about SCOPE_IDENTITY() function but it returns the last inserted PK only which does not really suit my need.

I could also use a while to do this one by one but since the number of data I’m planning to insert is immense, I feel like it’s not going to be the most optimized option.

Thanks for the answers in advance.

>Solution :

To grab multiple IDENTITY values from INSERT INTO SELECT FROM OUTPUT clause could be used:

-- temp table
CREATE TABLE #temp(col VARCHAR(100));
INSERT INTO #temp(col)  VALUES ('A'), ('B'), ('C');

--target table
CREATE TABLE tab(
  id INT IDENTITY,
  col VARCHAR(100)
);

Main insert:

INSERT INTO tab(col)
OUTPUT inserted.id, inserted.col
SELECT col
FROM #temp;

The output could be also Inserted into another table using OUTPUT INTO:

CREATE TABLE #temp_identity(id INT);

INSERT INTO tab(col)
OUTPUT inserted.id
INTO #temp_identity
SELECT col
FROM #temp;

SELECT * FROM #temp_identity;

db<>fiddle demo