Get the Id of Last Inserted Records

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.

how to get last insert id after insert query in codeigniter active record

Try this

function add_post($post_data){
   $this->db->insert('posts', $post_data);
   $insert_id = $this->db->insert_id();

   return  $insert_id;
}

In case of multiple inserts you could use

$this->db->trans_start();
$this->db->trans_complete();

Getting the id of last inserted record

I think you are missing a ; on line 67 after the last ".

How to get last inserted id?

For SQL Server 2005+, if there is no insert trigger, then change the insert statement (all one line, split for clarity here) to this

INSERT INTO aspnet_GameProfiles(UserId,GameId)
OUTPUT INSERTED.ID
VALUES(@UserId, @GameId)

For SQL Server 2000, or if there is an insert trigger:

INSERT INTO aspnet_GameProfiles(UserId,GameId) 
VALUES(@UserId, @GameId);
SELECT SCOPE_IDENTITY()

And then

 Int32 newId = (Int32) myCommand.ExecuteScalar();

Get the ID of last inserted records

SCOPE_IDENTITY() will correctly give you the LAST ID. What you need is to combine it with @@Rowcount to give you the range of IDs. As the other Richard points out, this only works if your increment is set to 1

For example:

declare @last int, @first int
insert ...
select @last = scope_identity(), @first = scope_identity() - @@rowcount + 1

Another way (use this in SQL Server 2008 for guaranteed results) to do this is to use the OUTPUT clause

declare @ids table (id int)
INSERT INTO Table1 (FirstName ,LastName ,EmailAddress)
output inserted.id into @ids

-- Get the ids
SELECT id from @Ids

The table now contains all the inserted ids

How do I get the last inserted ID of a MySQL table in PHP?

If you're using PDO, use PDO::lastInsertId.

If you're using Mysqli, use mysqli::$insert_id.

If you're still using Mysql:

Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial.

_{But if you have to, use mysql_insert_id.}

---

Related Topics