Get the last inserted row ID (with SQL statement)
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.
Select id from last inserted row
You can use the SCOPE_IDENTITY()
in your stored procedure to get last inserted record id.
SELECT SCOPE_IDENTITY() :- It will returns the last identity value inserted into an
identity column in the same scope.
SELECT @@IDENTITY :- @@IDENTITY will return the last identity value entered
into a table in your current session.
SELECT IDENT_CURRENT(‘tablename’) :- IDENT_CURRENT is not limited by scope and
session; it is limited to a specified table.
There are other options also such as
1. Select Top 1 id From Table Order by id desc
2. SELECT max(id) FROM table
Refer msdn http://msdn.microsoft.com/en-us/library/ms190315.aspx
and
http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/
how to get id of last inserted row
So a little trick to do this is to change you statement to this:
INSERT INTO tbl (...) VALUES (...); SELECT LAST_INSERT_ID()
and then insert the row with ExecuteScalar
which will return you the id; you can then update the object accordingly. So the entire snippet might look like this:
using (var conn = new MySqlConnection(connString))
using (var cmd = new MySqlCommand(sql, conn))
{
var result = cmd.ExecuteScalar();
int id;
if (int.TryParse(result, out id))
{
// set the object's id here
}
}
How get last inserted ID in SQL Server 2012
Assuming table is
create table test
(
id int IDENTITY(1,1) PRIMARY KEY
, testData varchar(100) NOT NULL
)
this works
INSERT INTO dbo.Test (testData) VALUES ('test')
SELECT @@IDENTITY AS ID
as does this (better answer)
INSERT INTO dbo.Test (testData) VALUES ('test')
SELECT SCOPE_IDENTITY() AS ID
I don't have enough information to know why yours does not work. It is probably in the table structure? I would need more information to answer this.
SQL Server - Best way to get identity of 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 theINSERT
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.
The last inserted row id in database is successfully inserted but to get that id is 0?
The LastId
function just steps through all rows in the table, without any specific order; it will return some random ID.
To get the last ID inserted in the same database connection, call the sqlite3_last_insert_rowid() function.
To get the ID with the largest value, execute SELECT MAX(ID) FROM ...
.
MS SQL Server Last Inserted ID
Try this one -
DECLARE @ID BIGINT
INSERT INTO dbo.TABLE_ID (Table_NAME)
SELECT 'Table_Products'
SELECT @ID = SCOPE_IDENTITY()
INSERT INTO dbo.Table_Products (ID, Product_Name)
SELECT @ID, 'SomeProduct'
How to get last inserted row from table?
row_number()
is the typical way of doing this:
select t.*
from (select t.*,
row_number() over (partition by id order by date desc) as seqnum
from t
) t
where seqnum = 1;
Related Topics
How to Create Multiple One to One's
Update Multiple Rows in Same Query Using Postgresql
Where Is |Datadirectory| Defined
Best Way to Check For "Empty or Null Value"
Default Row Order in Select Query - SQL Server 2008 VS SQL 2012
What Does the SQL Standard Say About Usage of Backtick(')
What's the Best Way to Join on the Same Table Twice
How to Use Parameters "@" in an SQL Command in Vb
Deleting Duplicate Rows from SQLite Database
Does SQLite3 Not Support Foreign Key Constraints
MySQL Select Dynamic Row Values as Column Names, Another Column as Value
Copy Tables from One Database to Another in SQL Server
Why No Windowed Functions in Where Clauses
Error 1046 No Database Selected, How to Resolve
Connect by Prior Equivalent For MySQL
Is There Something Wrong With Joins That Don't Use the Join Keyword in SQL or MySQL
How to Select an Entire Row Which Has the Largest Id in the Table