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

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.

CodePudding user response：

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

CodePudding user response：

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