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

Question

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.

This could be a good approach but the information in `#invoiceitems` table is not guaranteed to be unique thus we could have two different invoiceIDs with same information. @RobIII — Mateen Bagheri, Jul 17 '22 at 12:05
Perhaps the OUTPUT clause can be used? Exactly how is not clear since your definition of the temp table is not clear. — SMor, Jul 17 '22 at 12:10

score 6 · Accepted Answer · answered Jul 17 '22 at 12:11

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

el B · Answer 2 · 2022-07-17T12:50:19.643

CREATE TABLE #InvoiceItems(
    RowNumber INT, 
    SaleID INT,   
    BuyerID INT,   
    InvoiceID INT
);

INSERT INTO #InvoiceItems (RowNumber, SaleID, BuyerID) values (1, 55, 77)
INSERT INTO #InvoiceItems (RowNumber, SaleID, BuyerID) values (1, 56, 78)
INSERT INTO #InvoiceItems (RowNumber, SaleID, BuyerID) values (1, 57, 79)
INSERT INTO #InvoiceItems (RowNumber, SaleID, BuyerID) values (1, 58, 80)
INSERT INTO #InvoiceItems (RowNumber, SaleID, BuyerID) values (1, 59, 81)


DECLARE @Inserted table( RowNumber int,
                    SaleID INT,    
                    BuyerID INT,  
                    InvoiceID INT);

INSERT INTO dbo.[Invoice] (RowNumber, SaleID, BuyerID) 
OUTPUT  INSERTED.RowNumber, INSERTED.SaleID, INSERTED.BuyerID, INSERTED.InvoiceID
INTO @Inserted
SELECT RowNumber, SaleID, BuyerID 
FROM #InvoiceItems

UPDATE ii
SET InvoiceID = ins.InvoiceID
FROM #InvoiceItems ii 
JOIN @Inserted ins on ins.BuyerID = ii.BuyerID and ins.RowNumber = ii.RowNumber and ins.SaleID = ii.SaleID

SELECT * FROM #InvoiceItems

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

2 Answers2