sql get id of inserted row code example

Example 1: sql insert inserted id

-- Never use @@identity or scope_identity()
-- they can not always be relied upon

DECLARE @Ids_tbl TABLE ([id] INT); -- Requires table. use variable for scope
INSERT INTO [sometable] ([ColA],[ColB])
OUTPUT INSERTED.ID INTO @Ids_tbl(id)
VALUES ('valA','valB');

SELECT [id] FROM @Ids_tbl; -- <-- Id(s) in here

Example 2: how to get inserted id in sql

/*  inserted id in SQL Server */
-- SCOPE_IDENTITY() last identity generated for ANY TABLE in the CURRENT SESSION and the CURRENT SCOPE
    INSERT INTO TableA (...) VALUES (...)
    SET @LASTID = SCOPE_IDENTITY()
-- @@IDENTITY last identity generated for ANY TABLE in the CURRENT SESSION
    INSERT INTO TableA (...) VALUES (...)
    SET @LASTID = @@IDENTITY
-- IDENT_CURRENT('TableA') last identity for a SPECIFIC TABLE in ANY SESSION and ANY SCOPE
    SET @LASTID = IDENT_CURRENT('TableA')
-- OUTPUT clause of the INSERT statement EVERY ROW inserted via that STATEMENT
    DECLARE @NewIds TABLE(ID INT, ...)
    INSERT INTO TableA (...)
    OUTPUT Inserted.ID, ... INTO @NewIds
    SELECT ...

Example 3: mysql id of inserted row

-- For mysql
INSERT INTO table_name (col1, col2,...) VALUES ('val1', 'val2'...);
SELECT LAST_INSERT_ID();