This question already has answers here :
If your SQL Server table has a column of type INT IDENTITY (or BIGINT IDENTITY), then you can get the latest inserted value using:
INSERT INTO dbo.YourTable(columns....)
VALUES(..........)
SELECT SCOPE_IDENTITY()
This works as long as you haven't inserted another row - it just returns the last IDENTITY value handed out in this scope here.
There are at least two more options - @@IDENTITY and IDENT_CURRENT - read more about how they works and in what way they're different (and might give you unexpected results) in this excellent blog post by Pinal Dave here .
How To Get Last Inserted ID On SQL Server, How do I get the last inserted row ID in SQL? The stored procedure inserts a row into the accounts table, get the account id using the LAST_INSERT_ID() function, and use this account id for inserting a phone into the phones table. A row in the phones table should only exist if there is a corresponding row in the accounts table, therefore, we put both inserts into a transaction .
Assuming a simple table:
CREATE TABLE dbo.foo(ID INT IDENTITY(1,1), name SYSNAME);
We can capture IDENTITY values in a table variable for further consumption.
DECLARE @IDs TABLE(ID INT);
-- minor change to INSERT statement; add an OUTPUT clause:
INSERT dbo.foo(name)
OUTPUT inserted.ID INTO @IDs(ID)
SELECT N'Fred'
UNION ALL
SELECT N'Bob';
SELECT ID FROM @IDs;
The nice thing about this method is (a) it handles multi-row inserts (SCOPE_IDENTITY() only returns the last value) and (b) it avoids this parallelism bug , which can lead to wrong results
Best way to get last identity inserted in a table, ; You can use query like this inside stored procedure or as an ad-hoc query. SCOPE_IDENTITY is most recommended function to get last identity inserted value in SQL Server. It will return a value on the same scope and same session. Let’s do a simple insert on Student table and to get last identity inserted value.
You can use:
SELECT IDENT_CURRENT('tablename')
to access the latest identity for a perticular table.
e.g. Considering following code:
INSERT INTO dbo.MyTable(columns....) VALUES(..........)
INSERT INTO dbo.YourTable(columns....) VALUES(..........)
SELECT IDENT_CURRENT('MyTable')
SELECT IDENT_CURRENT('YourTable')
This would yield to correct value for corresponding tables.
It returns the last IDENTITY value produced in a table, regardless of the connection that created the value, and regardless of the scope of the statement that produced the value.
IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope.
How to get last inserted id in laravel PHP Framework, How do you get the ID of the inserted row in SQL? Then for getting last inserted id use this for table "user" seq column name "id" SELECT currval(pg_get_serial_sequence('users', 'id'));
MySQL Last Insert ID Explanation: Learn to Use Mysql_insert_id, How can I get last ID inserted in MySQL using PHP? Most of the time this works fine, but sometimes a trigger will go and insert a new row that you don't know about, and you'll get the ID from this new row, instead of the one you want. SCOPE_IDENTITY() solves this problem. It returns the id of the last thing that you inserted in the SQL code you sent to the database. If triggers go and create extra rows, they won't cause the wrong value to get returned.
28.7.28.3 How to Get the Unique ID for the Last Inserted Row, 28.7. 28.3 How to Get the Unique ID for the Last Inserted Row. For information on LAST_INSERT_ID() , which can be used within an SQL statement, see Section 12.15, “Information Functions”. For information on mysql_insert_id() , the function you use from within the C API, see Section 28.7. There is no way to ask SQL Server which row was inserted last unless you are doing so in the same batch as the insert. For example, if your table has an IDENTITY column, you can use SCOPE_IDENTITY () (never use @@IDENTITY, since that can be unreliable if you have or will ever add triggers to the source table):
Access last inserted row in MySQL?, This method gets the ID of the last inserted record in MySQL. The syntax is as follows. SELECT LAST_INSERT_ID();. To understand the above Get ID of The Last Inserted Record If we perform an INSERT or UPDATE on a table with an AUTO_INCREMENT field, we can get the ID of the last inserted/updated record immediately. In the table "MyGuests", the "id" column is an AUTO_INCREMENT field: CREATE TABLE MyGuests (
Comments Is ID is identity primary key? @Thit what difference does it make if it is the primary key? IDENTITY and primary key are two completely different concepts. While you may often see them associated together, they are not the same thing. I just blogged about this misconception today, actually: sqlblog.com/blogs/aaron_bertrand/archive/2012/02/27/… Then @marc_s already provided you for answer. Note that SCOPE_IDENTITY() can yield incorrect results as well (under parallelism), see support.microsoft.com/kb/2019779 - the fix was first made available last week for 2008 R2 SP1 CU5. In all earlier versions, the workarounds are to set maxdop to 1, keep a fixed plan that happens to not use parallelism (I have not tested this), or use the output clause. I suspect Pinal Dave is wrong to say scope_identity "will return the last identity value that you explicitly created, rather than any identity that was created by a trigger or a user defined function" - I insert into a table with a trigger that causes an insert into an audit table and scope_identity returns null to me. In fact, the only option that doesn't return null is @@identity - but it is giving me the audit table's newest ID! So None of these methods return the latest inserted ID in my original insert. SQL Server 2016. @youcantryreachingme: I suspect you have an error somewhere in your code - Pinal Dave typically isn't wrong, and most definitely not on this point .. why don't you put your observations into a question and ask it here? @marc_s - happy to be shown where my error is. Here is a script that recreates the scenario - wetransfer.com/downloads/… @youcantryreachingme: well, your table Client does NOT have any IDENTITY column - so a INSERT INTO Client()... obviously will NOT SCOPE_IDENTITY() will rightfully be NULL :.... correctness is only important if you view programming as something other than job security through creation of obscure bugs </sarcasm> I wish there was a shorthand syntax to put the value directly into a variable when you know there is only one result, and I would be happy with it either throwing an error if there are multiple rows (as it does for subqueries in some contexts) or just using the value of the last row (similar to behavior of an update statement that updates a single row based on input of multiple joined rows). Would this still be the case within a transaction? Based on your statement: "regardless of the connection that created the value" - that sounds useless - if another connection inserted a row right after mine, I'd get his number - which I would later use to attempt to update the row I inserted, but instead, would update his. Right? You use ‘ and ’ characters, which are not valid SQL. Instead, use ' character which is valid SQL. I have fixed this.
