How to Get the Identity of an Inserted Row in SQL

In databases, Sometimes we need the identity of an inserted row in SQL. The identity value of the newly inserted row is very useful for various purposes in the database. In this article, we will learn how to get the identity of an inserted row in SQL.

In SQL, the RETURNING INTO clause within an INSERT statement enables retrieval of the inserted row’s identity. This clause facilitates capturing and storing the generated identity value in a variable, enhancing data management capabilities efficiently.

How to Retrieve Identity

To obtain the identity of an inserted row in SQL, employ the RETURNING clause after the INSERT statement. This clause enables capturing the generated identity value into a variable.

Examples of How to Retrieve the Identity of an Inserted Row in SQL

Firstly, let’s create a table named students with attributes StudentID, FirstName, LastName, and Age.

Create Table:

CREATE TABLE Students (
    StudentID NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
    FirstName VARCHAR2(50),
    LastName VARCHAR2(50),
    Age INT
);

Output:

Create Table

Example 1: Insert a Row and Retrieve Identity

This example inserts a new student named John Doe, aged 20, into the Students table. It retrieves the generated StudentID using the RETURNING clause and stores it in the new_student_id variable. Finally, it displays the obtained StudentID to confirm the successful insertion. After executing the below code, you should see the output displaying the identity of the newly inserted row:

DECLARE
    new_student_id NUMBER;
BEGIN
    INSERT INTO Students (FirstName, LastName, Age)
    VALUES ('John', 'Doe', 20)
    RETURNING StudentID INTO new_student_id;
    
    DBMS_OUTPUT.PUT_LINE('New StudentID: ' || new_student_id);
END;
/

Output:

How to get the identity of an inserted row in SQL

Explanation: The output displays the newly assigned StudentID generated upon inserting a new record into the Students table. The DBMS_OUTPUT.PUT_LINE function prints “New StudentID:” followed by the value of new_student_id, providing confirmation of the successful insertion with the assigned ID.

Example 2: Insert Multiple Rows and Retrieve Identities

This example inserts multiple students into the Students table: Jane Smith, aged 22, and Michael Johnson, aged 25. It retrieves the StudentID for each insertion using the RETURNING clause and stores them in new_student_id1 and new_student_id2 variables, respectively. Finally, it displays the obtained StudentIDs for confirmation. In this example, we will insert multiple rows and we’ll retrieve identities for each.

DECLARE
    new_student_id1 NUMBER;
    new_student_id2 NUMBER;
BEGIN
    INSERT INTO Students (FirstName, LastName, Age)
    VALUES ('Jane', 'Smith', 22)
    RETURNING StudentID INTO new_student_id1;

    INSERT INTO Students (FirstName, LastName, Age)
    VALUES ('Michael', 'Johnson', 25)
    RETURNING StudentID INTO new_student_id2;

    DBMS_OUTPUT.PUT_LINE('New StudentID 1: ' || new_student_id1);
    DBMS_OUTPUT.PUT_LINE('New StudentID 2: ' || new_student_id2);
END;
/

Output:

Insert Multiple Rows and Retrieve Identities:

Explanation:

• This example shows how to insert multiple records into a database table, with each record receiving a unique identifier.
• By using the RETURNING clause, the newly generated identifiers for each record insertion are captured and presented systematically using PL/SQL’s DBMS_OUTPUT.PUT_LINE function.

Example 3: Insert Rows Using Select Statement and Retrieve Identities

In this example, we are inserting rows into the database table by selecting information from an existing dataset and retrieving the identity of the inserted row using the RETURNING INTO clause.

DECLARE
    new_student_id NUMBER;
BEGIN
    INSERT INTO Students (FirstName, LastName, Age)
    VALUES ('Emily', 'Brown', 21);

    SELECT StudentID INTO new_student_id
    FROM Students
    WHERE FirstName = 'Emily'
    AND LastName = 'Brown'
    AND Age = 21;

    DBMS_OUTPUT.PUT_LINE('New StudentID: ' || new_student_id);
END;
/

Output:

Insert Rows Using Select Statement and Retrieve Identities

Explanation:

• In this scenario, records are added to the database table by selecting information from an existing dataset.
• The unique identifier assigned to the newly added record is acquired through the RETURNING clause and it showcased via PL/SQL’s DBMS_OUTPUT.PUT_LINE function.

Conclusion

It is important to get the identity of an inserted row in SQL for managing data in databases. This can be achieved using the ‘RETURNING INTO’ clause when inserting data, you can do this using the `RETURNING INTO` part when inserting data. Following the steps we’ve covered, you can easily handle and use these unique identifiers for your records.