如何检索INSERT INTO〔table〕SELECT〔col1,…〕中完成的所有插入的SCOPE_IDENTITY

获取多个 IDENTITY 值来自 INSERT INTO SELECT FROM OUTPUT 子句可以使用:

-- 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)
);

主插件:

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

也可以使用将输出插入到另一个表中 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

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