How to get inserted IDs of multiple rows on one INSERT?

240
May 01, 2018, at 05:11 AM

I want to insert multiple rows into a table, using a single INSERT statement. This is no problem, since SQL offers the option to provide multiple rows as parameter for a single INSERT statement. Now, those rows contain an ID field that is incremented automatically, i.e. its value is set by the database, not by my code.

As a result, I would like to get the ID values of the inserted rows. My basic question is: How do I do that for MariaDB / MySQL?

As it turns out, this is pretty simple, e.g. in PostgreSQL, as PostgreSQL has the RETURNING clause for INSERT which returns the desired values for one or even for multiple rows. This is exactly what I want and it works.

Unfortunately, neither MariaDB nor MySQL have PostgreSQL's RETURNING clause, so I need to fallback to something such as LAST_INSERT_ID(), but this only returns the ID of the single last inserted row, even if multiple rows were inserted using a single INSERT. How do I get all the ID values?

My code currently looks like this:

INSERT INTO mytable
  (foo, bar)
VALUES
  ('fooA', 'barA'),
  ('fooB', 'barB');
SELECT LAST_INSERT_ID() AS id;

How can I solve this issue in a way that works even with concurrent writes?

(And no, it's not an option to change to a UUID field, or something like this; the auto-increment field is given, and can not be changed.)

Answer 1

MySQL & MariaDB have the LAST_INSERT_ID() function, and it returns the id generated by the most recent INSERT statement.

But when your INSERT statement inserts multiple rows, LAST_INSERT_ID() returns the first id in the set generated.

In such a batch of multiple rows, you can rely on the subsequent id's being consecutive. The MySQL JDBC driver depends on this, for example.

If the rows you insert include a mix of NULL and non-NULL values for the id column, you have a risk of messing up this assumption. The JDBC driver returns the wrong values for the set of generated id's.

Answer 2

As stated in the comments, you can capture the inerted IDs (SQL Server):

use tempdb
create table test (
    id int identity(1,1) primary key,
    t varchar(10) null
)
create table ids (
    i int not null
)
insert  test(t)
output  inserted.id into ids
values (null), (null), (null)

select  *
from    test
select  *
from    ids
READ ALSO
XQuery - “NOT IN” equivalent not working as expected

XQuery - “NOT IN” equivalent not working as expected

I am new to XQuery (coming from SQL) and I am trying to write a query that selects all results that do not exist in another table

134
Symfony 3.4 getDoctrine() results in error

Symfony 3.4 getDoctrine() results in error

I have seen some posts about this topic but not the answer I am looking forWhen I call $this->getDoctrine(); from a method say public function returnObjectAction(){} and the Class implements use Symfony\Bundle\FrameworkBundle\Controller\Controller...

160
SQL innerjoin convert integer to text

SQL innerjoin convert integer to text

I have an SQL database directorydb that holds two tables: departments and persons

107
display username AFTER user has logged in PHP MySQL

display username AFTER user has logged in PHP MySQL

I've built a simple user register and login system, using PHP and bootstrapI am a static designer so PHP is very new to me and I'm struggling with the syntax and logic so far, but I am enjoying it

131