SQL

sql get last primary key inserted

-- @@IDENTITY - Returns the last identity created in the same session
-- SCOPE_IDENTITY() - Returns the last identity created in the same session and the same scope
-- IDENT_CURRENT() - Returns the last identity created for a specific table or view in any session
-- OUTPUT Inserted.[ColumnName] INTO [Table] - Returns a list of created identities (used for multi inserts)

/*Examples Start*/
IF OBJECT_ID('tempdb..#tmpT') IS NOT NULL
	DROP TABLE #tmpT

IF OBJECT_ID('TableA') IS NOT NULL
	DROP TABLE TableA

CREATE TABLE TableA (
	ID NUMERIC(18,0) IDENTITY(1,1),
	Name VARCHAR(255),
	PRIMARY KEY (ID)
)

INSERT INTO TableA (Name) VALUES ('Jane')
SELECT @@IDENTITY AS [ID]

INSERT INTO TableA (Name) VALUES ('John')
SELECT SCOPE_IDENTITY() AS [ID]

INSERT INTO TableA (Name) VALUES ('Smith')
SELECT IDENT_CURRENT('dbo.TableA') AS [ID]

DECLARE @NewIds TABLE(ID NUMERIC(18,0))
INSERT INTO TableA (Name)
OUTPUT Inserted.ID INTO @NewIds
VALUES ('Joe'), ('Bloggs')
SELECT * FROM @NewIds

CREATE TABLE #tmpT (ID NUMERIC(18,0))
INSERT INTO TableA (Name)
OUTPUT Inserted.ID INTO #tmpT
VALUES ('Joe'), ('Bloggs')
SELECT * FROM #tmpT
/*Examples End*/

get primary key of last inserted record sql server

CREATE TABLE #a(identity_column INT IDENTITY(1,1), x CHAR(1));
INSERT #a(x) VALUES('a');

SELECT SCOPE_IDENTITY();

-- 4 ways to get identity IDs of inserted rows in SQL Server
INSERT INTO TableA (...) VALUES (...)
SELECT @@IDENTITY

INSERT INTO TableA (...) VALUES (...)
SELECT SCOPE_IDENTITY()

SELECT IDENT_CURRENT('dbo.TableA')

DECLARE @NewIds TABLE(ID INT, ...)
INSERT INTO TableA (...)
OUTPUT Inserted.ID, ... INTO @NewIds
SELECT ...

How to get last inserted primary key in SQL Server

-- 4 ways to get identity IDs of inserted rows in SQL Server

INSERT INTO TableA (...) VALUES (...)
SET @LASTID = @@IDENTITY

INSERT INTO TableA (...) VALUES (...)
SET @LASTID = SCOPE_IDENTITY()

SET @LASTID = IDENT_CURRENT('dbo.TableA')

DECLARE @NewIds TABLE(ID INT, ...)

INSERT INTO TableA (...)
OUTPUT Inserted.ID, ... INTO @NewIds
SELECT ...

get last inserted primary key

INSERT INTO dbo.Table(columns)
OUTPUT INSERTED.p_key, INSERTED.someothercolumnhere .......
VALUES(...)