fetching newly added auto increment value using OUTPUT clause of INSERT statement

Bandi
Posted by Bandi under Sql Server category on | Points: 40 | Views : 338
OUTPUT option with INSERT helps you to fetch last inserted ID value. It is very specific to current session , current scope and object-specific.

Below is the code snippet.
CREATE TABLE IdentityTable ( ID INT IDENTITY(1,1), Name VARCHAR(100))
GO
INSERT INTO IdentityTable (Name)
OUTPUT inserted.ID
VALUES( 'Insert First Record')
GO
/*
ID
1
*/

DROP TABLE IdentityTable

If you needed to use this value further, then you should assign to one table variable

CREATE TABLE IdentityTable ( ID INT IDENTITY(1,1), Name VARCHAR(100))
GO

DECLARE @TableVariable TABLE( HoldIdentity INT)

INSERT INTO IdentityTable (Name)
OUTPUT inserted.ID INTO @TableVariable
VALUES( 'Insert First Record')

SELECT * FROM @TableVariable
/*
HoldIdentity
1
*/
DROP TABLE IdentityTable

Comments or Responses

Login to post response