After inserting a row, the row identifier must be carried forward to be utilized for other related tables. When writing a batch process, such as a stored procedure, this is a common case.
Consider the following use cases:
- You are inserting information about a specific transaction, however, the information about the transaction is being stored in various tables.
- Your application accepted the product order information; however, the order summary and details are stored in multiple tables in the database.
In such cases, the identity value must be passed to the next table to ensure that the order summary and order details can be linked. The same is true for the transaction.
In this post, we’ll look at how to get the last identity entered value so that it may be used for the next table or transaction.
MySQL:
The last inserted id will be obtained using the built-in function LAST_INSERT_ID(). For an insert statement on an auto-increment column, this MySQL method returns the BIG UNSIGNED value. There is another way to get the most recently auto-inserted data, which is to query the information schema table. Both the methods are demonstrated in the following example.
Example:
CREATE TABLE tbStudent(
iStudentID INT AUTO_INCREMENT PRIMARY KEY,
vcStudentName VARCHAR(20));
INSERT INTO tbStudent VALUES
(1001, ‘Shafi’), (1002, ‘Shaik’),(1003,’Zafar’),(1004,’Tahir’);
— METHOD-1
INSERT INTO tbStudent (vcStudentName) VALUES (‘Hadi’);
SET @LastIdentityValue = LAST_INSERT_ID();
SELECT @LastIdentityValue;
— METHOD-2
INSERT INTO tbStudent (vcStudentName) VALUES (‘Adnan’);
SELECT @LastAutoIncrementValue := AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ‘tbStudent’;
SELECT @LastAutoIncrementValue;
In SQL Server, there are three built-in functions available that picks up the last inserted identity value.
It returns the most recent IDENTITY value produced on a connection, independent of the table that generated the value or the scope of the statement that generated the data. @@IDENTITY returns the most recently entered identity value into a table in your current session. @@IDENTITY is confined to the current session, but not to the current scope. If you wish to restrict it to the current session and scope, use @@SCOPE_IDENTITY. If you wish not to restrict to any scope or session then use IDENT_CURRENT which is however limited to a specific table.
Example:
DECLARE @LastIdValue INT
CREATE TABLE tbStudentDetais(
iStudentID INT IDENTITY(1,1),
vcStudentName VARCHAR(100),
TotalMarks INT);
INSERT INTO tbStudentDetais VALUES
(‘Zafar Iqbal’, 580),
(‘Tahir Iqbal’, 580)
SELECT @LastIdValue = SCOPE_IDENTITY()
SELECT @LastIdValue
For more information about IDENTITY functions in SQL Server, please click here.
Hope you find this article informative.
Happy learning!!
Big Data and SQL 