SQL

sql output inserted id

-- @@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*/