Ms SQL Server Last Inserted Id

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 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.

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'

Getting the id of the last inserted record from an MSSQL table using PDO and PHP

If your id column is named id you can use OUTPUT for returning the last inserted id value and do something like this:

    $CustID = "a123";
    $Name="James"
    $stmt = "INSERT INTO OrderHeader (CustID, Name) 
             OUTPUT INSERTED.id
             VALUES (:CustID, :Name)";

    $stmt = $db->prepare( stmt  );
    $stmt->bindParam(':CustID', $CustID);       
    $stmt->bindParam(':Name', $Name);
    $stmt->execute();

    $result = $stmt->fetch(PDO::FETCH_ASSOC);
    echo $result["id"]; //This is the last inserted id returned by the insert query

Read more at:

https://msdn.microsoft.com/en-us/library/ms177564.aspx

http://php.net/manual/es/pdo.lastinsertid.php

How to get last inserted ID with GO-MSSQLDB driver?

The reason for this is because PostgreSQL does not return you the last inserted id. This is because last inserted id is available only if you create a new row in a table that uses a sequence.

If you actually insert a row in the table where a sequence is assigned, you have to use RETURNING clause. Something like this: INSERT INTO table (name) VALUES("val") RETURNING id.

I am not sure about your driver, but in pq you will do this in the following way:

lastInsertId := 0
err = db.QueryRow("INSERT INTO brands (name) VALUES($1) RETURNING id", name).Scan(&lastInsertId)

---

Related Topics