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