How to Get the Insert ID in SQL?

When working with SQL databases, obtaining the insert ID (the auto-incremented primary key value) after inserting a new record is crucial for managing data relationships and ensuring seamless data referencing. In SQL databases, obtaining the insert ID after adding a new record to a table is a common requirement for various applications. The insert ID, also known as the auto-incremented primary key value, uniquely identifies the newly inserted row.

This article explains the methods to retrieve the insert ID in SQL databases, providing insights into its significance and practical applications, and demonstrating examples for various database systems.

Why Retrieve the Insert ID?

The insert ID serves as a unique identifier for each record added to a table, enabling efficient data retrieval and manipulation. When working with SQL databases, obtaining the insert ID after adding a new record is a common requirement for various applications. The insert ID, also known as the auto-incremented primary key value, uniquely identifies the newly inserted row. Let's explore different methods to retrieve the insert ID in SQL databases:

• Using LAST_INSERT_ID() Function
  
• Using SCOPE_IDENTITY() Function (for SQL Server)

1. Using LAST_INSERT_ID() Function

One of the most common ways to retrieve the insert ID is by using the LAST_INSERT_ID() function. This function returns the auto-generated ID of the most recent insert operation within the current session.

Query:

INSERT INTO table_name (column1, column2) VALUES (value1, value2);
SELECT LAST_INSERT_ID();

Key Terms

• INSERT INTO: Adds new rows of data into a table.
  
• VALUES: Indicates the beginning of the values to be inserted.
  
• SELECT: Retrieves data from a database.
  
• LAST_INSERT_ID(): A function that returns the last automatically generated value inserted into an AUTO_INCREMENT column in the current session.

Example: Using LAST_INSERT_ID() Function

Consider a scenario where we have a table named customers with an auto-incremented primary key column customer_name. After inserting a new customer record, we can retrieve the insert ID as follows:

Query:

CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(50)
);

-- Insert a new customer
INSERT INTO customers (customer_name) VALUES ('Alice');

-- Retrieve the auto-generated ID of the last inserted row
SELECT LAST_INSERT_ID() AS last_inserted_id;

Output

Explanation:

The output '1' signifies that 'Alice' was the first customer inserted, receiving the ID 1 due to the auto-increment feature. The query retrieves this auto-generated ID of the last inserted row, confirming the successful insertion of 'Alice' into the database.

2. Using SCOPE_IDENTITY() Function (for SQL Server)

In SQL Server, the SCOPE_IDENTITY() retrieves the last identity value generated in the current execution scope, ensuring precision within the transaction.

Query:

INSERT INTO table_name (column1, column2) VALUES (value1, value2);
SELECT SCOPE_IDENTITY();

Key Terms

• INSERT INTO: Adds new rows of data into a table.
  
• VALUES: Indicates the beginning of the values to be inserted.
  
• SELECT: Retrieves data from a database.
  
• SCOPE_IDENTITY(): It retrieves the last identity value generated within the current scope (usually the current statement) in SQL Server.

Example

Consider a scenario where we have a table named employees with an auto-incremented primary key column employee_id. After inserting a new employee record, we can retrieve the insert ID as follows:

Using SCOPE_IDENTITY() Function

-- Create the employees table
CREATE TABLE employees (
    employee_id INT PRIMARY KEY IDENTITY,
    employee_name NVARCHAR(50)
);

-- Insert a row into the employees table
INSERT INTO employees (employee_name) VALUES ('John Doe');

-- Retrieve the last inserted ID using SCOPE_IDENTITY()
SELECT SCOPE_IDENTITY();

Output

Explanation:

The output '1' indicates that the employee 'John Doe' was inserted into the database, receiving the unique identifier 1. This value is retrieved using SCOPE_IDENTITY(), confirming the successful addition of 'John Doe' to the employees table as the first entry.

Conclusion

Retrieving the insert ID in SQL databases is crucial for data referencing and manipulation. By utilizing functions like LAST_INSERT_ID() or SCOPE_IDENTITY() developers can efficiently access the auto-generated ID of newly inserted records. Understanding these methods enhances database functionality and data management processes. By incorporating these various methods, we can effectively retrieve the insert ID in SQL databases, catering to different database systems and requirements.