Saving multiple scope_identity

1

Hello,
I need to move some records from one table (staging area)
to another exactly similar.
Both tables, tough, have PK in first column,
and I need to save the new IDs inserted in the ProdTable.

Something similar to:

INSERT INTO ProdTable(Field1, Field2)
SELECT Field1, Field2
FROM StagingTable
WHERE UserId = 10

How can I save (for future use) these new inserted Ids?

Thank you in advance.

Luis

2

Look into using the OUTPUT clause for INSERT similar to this:

IF OBJECT_ID('dbo.ProdTable') IS NOT NULL DROP TABLE dbo.ProdTable;

CREATE TABLE dbo.ProdTable
(
	Field1 INT NOT NULL IDENTITY(100,5) PRIMARY KEY,
	Field2 VARCHAR(64) NOT NULL
);


DECLARE @MyNewTableIDs TABLE
( 
	NewIDValues INT NOT NULL PRIMARY KEY
);  

INSERT dbo.ProdTable (Field2)
OUTPUT INSERTED.Field1
INTO @MyNewTableIDs
VALUES ('First Value'),
	('Second Value'),
	('Third Value');  
  
SELECT * FROM @MyNewTableIDs;  
SELECT * FROM dbo.ProdTable;
2 Likes
3

Thank you very much James.

Luis