Getting New Ids After Insert

Getting new IDs after insert

Use the OUTPUT functionality to grab all the INSERTED Id back into a table.

CREATE TABLE MyTable
(
    MyPK INT IDENTITY(1,1) NOT NULL,
    MyColumn NVARCHAR(1000)
)

DECLARE @myNewPKTable TABLE (myNewPK INT)

INSERT INTO 
    MyTable
(
    MyColumn
)
OUTPUT INSERTED.MyPK INTO @myNewPKTable
SELECT
    sysobjects.name
FROM
    sysobjects

SELECT * FROM @myNewPKTable

Get the new record primary key ID from MySQL insert query?

You need to use the LAST_INSERT_ID() function: http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id

Eg:

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

This will get you back the PRIMARY KEY value of the last row that you inserted:

The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client.

So the value returned by LAST_INSERT_ID() is per user and is unaffected by other queries that might be running on the server from other users.

SQL Server - Return value after INSERT

No need for a separate SELECT...

INSERT INTO table (name)
OUTPUT Inserted.ID
VALUES('bob');

This works for non-IDENTITY columns (such as GUIDs) too

How to get the identity of an inserted row?

• @@IDENTITY returns the last identity value generated for any table in the current session, across all scopes. You need to be careful here, since it's across scopes. You could get a value from a trigger, instead of your current statement.

• SCOPE_IDENTITY() returns the last identity value generated for any table in the current session and the current scope. Generally what you want to use.

• IDENT_CURRENT('tableName') returns the last identity value generated for a specific table in any session and any scope. This lets you specify which table you want the value from, in case the two above aren't quite what you need (very rare). Also, as @Guy Starbuck mentioned, "You could use this if you want to get the current IDENTITY value for a table that you have not inserted a record into."

• The OUTPUT clause of the INSERT statement will let you access every row that was inserted via that statement. Since it's scoped to the specific statement, it's more straightforward than the other functions above. However, it's a little more verbose (you'll need to insert into a table variable/temp table and then query that) and it gives results even in an error scenario where the statement is rolled back. That said, if your query uses a parallel execution plan, this is the only guaranteed method for getting the identity (short of turning off parallelism). However, it is executed before triggers and cannot be used to return trigger-generated values.

How to get inserted IDs of multiple rows on one INSERT?

MySQL & MariaDB have the LAST_INSERT_ID() function, and it returns the id generated by the most recent INSERT statement in your current session.

But when your INSERT statement inserts multiple rows, LAST_INSERT_ID() returns the first id in the set generated.

In such a batch of multiple rows, you can rely on the subsequent id's being consecutive. The MySQL JDBC driver depends on this, for example.

If the rows you insert include a mix of NULL and non-NULL values for the id column, you have a risk of messing up this assumption. The JDBC driver returns the wrong values for the set of generated id's.

List of inserted ID in SQL Server

Try merge trick with linking source rows with inserted identity values:

merge MyTABLE t
using @temp s
on 1=0
when not mathed then
insert ...
output inserted.ID, s.tempID 
into @linked_ids(id1, id2);

Get the LIST of last inserted id(s) after INSERT INTO in the same mysql table

I believe you are looking for this simple query:

select id
from my_table
where id >= LAST_INSERT_ID();

As explained in the documentation:

With no argument, LAST_INSERT_ID() returns a BIGINT UNSIGNED
(64-bit) value representing the first automatically generated
value successfully inserted for an AUTO_INCREMENT column as a result
of the most recently executed INSERT statement.

How do I get the ID of multiple inserted rows in MySQL?

1. You get it via SELECT LAST_INSERT_ID(); or via having your framework/MySQL library (in whatever language) call mysql_insert_id().

2. That won't work. There you have to query the IDs after inserting.

---

Related Topics