Bulk Insert and Identity Value

When we copy data from one table to another table using the INSERT INTO command, the LAST_INSERT_ID(), will return only the ID of the first row (Last Insert Id is 1).

Example

In this example, a series of SQL statements is presented to illustrate the process of inserting data into the “IT_Suppliers” table by selecting values from the “IT_Customers” table. Additionally, the last inserted ID is retrieved. Let’s break down each part:

INSERT INTO IT_Suppliers
(supplier_name)
SELECT customer_name
FROM IT_Customers
ORDER BY CUST_Id;
SELECT LAST_INSERT_ID();
SELECT* FROM IT_Suppliers

Output:

Explanation:

In the above example, 2 tables namely IT_Suppliers and IT_Customers are used. Data is copied from the IT_Customers to the IT_Suppliers table using the INSERT INTO command and SELECT query. It can be noted that 3 rows were inserted from IT_Customers to IT_Suppliers and when we check the LAST_INSERT_ID, it returns only the value 1, which is the identity value from the first row inserted.

The LAST_INSERT_ID() returns the identity value after an insert into the specific table.

Syntax: SELECT LAST_INSERT_ID();

Getting the ID of a column after an insert may be required during database programming and in this article, we will learn how to get the ID of the inserted row.

In MySQL, the Key column of a table can be set to auto-increment with the starting value. The auto-increment column helps to avoid duplicate IDs being created or inserted.