How to get last inserted id in sql?

To get the last inserted ID in SQL, the method depends on the database system you’re using. Here are the most common ways to retrieve it:

1. Using LAST_INSERT_ID() (MySQL, MariaDB)

If you're using MySQL or MariaDB, you can use:

  SELECT LAST_INSERT_ID();

• This function returns the last auto-incremented ID generated in the current session.
  
• Works best when inserting data into a table with an AUTO_INCREMENT primary key.
  

Example:

INSERT INTO Employees (name, salary) VALUES ('John Doe', 50000);
SELECT LAST_INSERT_ID();

2. Using SCOPE_IDENTITY() (SQL Server)

For Microsoft SQL Server, use:

  SELECT SCOPE_IDENTITY();

• It returns the last identity value inserted in the same scope (like a stored procedure or batch).
  
• Safer than @@IDENTITY, which may return an ID from a trigger.
  

Example:

INSERT INTO Employees (name, salary) VALUES ('Jane Doe', 60000);
SELECT SCOPE_IDENTITY();

3. Using RETURNING (PostgreSQL, Oracle)

PostgreSQL and Oracle allow fetching the ID during the insert itself:

  INSERT INTO Employees (name, salary) VALUES ('Alice', 70000) RETURNING id;

This directly returns the generated ID, making it efficient.

4. Using IDENTITY() (SQL Server - Alternative)

  SELECT IDENT_CURRENT('Employees');

Returns the last identity value generated for a specific table.

Summary:

• MySQL: LAST_INSERT_ID()
  
• SQL Server: SCOPE_IDENTITY()
  
• PostgreSQL/Oracle: RETURNING
  
• General alternative: IDENT_CURRENT('table_name')
  

Each method ensures you get the most recently inserted auto-incremented ID. Let me know if you need more details!

To get the last inserted ID in SQL, you typically use the SCOPE_IDENTITY() function in SQL Server. This function returns the last identity value inserted into an identity column in the same scope (session, batch, or stored procedure).

Here's an example of how you can use SCOPE_IDENTITY():
-- Insert a row into a table with an identity column
INSERT INTO YourTable (Column1, Column2)
VALUES ('Value1', 'Value2');-- Get the last inserted ID
SELECT SCOPE_IDENTITY() AS LastInsertedId;

In this example:

• Replace YourTable with the name of your table.
  
• Replace Column1 and Column2 with the columns you're inserting values into.
  
• 'Value1' and 'Value2' are example values you're inserting into the table.

After inserting a row into the table, you can use SCOPE_IDENTITY() in the same scope to retrieve the last inserted ID.

It's important to note that SCOPE_IDENTITY() returns the last identity value inserted by the current session, and it's not affected by triggers or concurrent sessions inserting into the same table. If you're working with SQL Server, it's the most commonly used function for this purpose. However, other database systems like MySQL or PostgreSQL may have different methods to achieve the same result.