LAST_INSERT_ID() with multiple records insert MySQL

Question

I have an sql multiple records INSERT, with an 'id' field set to AUTO_INCREMENT, something like this:

 INSERT INTO table1 VALUES (null, "bla"),(null, "bla"),(null, "bla"),(null, "bla") etc..;

and then i have another multiple records INSERT:

INSERT INTO table2 VALUES (null, "bla"),(null, "bla"),(null, "bla"),(null, "bla") etc..;

Now i want the 'id2' of table2 to be the same of table1, but i don't know how to remember all the last_insert_ids from table1 to insert them to table2, if i use LAST_INSERT_ID() it gives me only the 'id' of the first inserted value. Does anyone know how to take all the last_insert_ids?

If you use auto increment you just need one ID plus the number of records you inserted to know all the IDs because you always increment by 1. Or did I misunderstand you? — Simeon
I am building this string queries dynamically and in the same loop, so i was wondering if there was a way to build both the queries in 1 loop, instead of 2. — Blackecho
my suggestion : each time you put last inserted id into array and when finish it's use then overwrite with another insert id. — Anand Somasekhar

rashmani rashmani · Accepted Answer · 2014-04-14T12:11:28

if I understand correctly, indexes in your second table are references (like FK) to indexes in your first table. In this case you could:

insert a record in first table
get last_insert_id() and store it in an array
loop 1-2 'til you're done
build an insert query using your array values

In PHP this would be something like:

var arrInserted = array();

foreach($newRecords as $newRecord) {
 $sql = "INSERT INTO table1 ($newRecord->valBlah);";
 $stmt = $dbh->prepare($sql);  
 $stmt->execute();

 $sql = "SELECT LAST_INSERT_ID() AS myNewID;";
 $stmt = $dbh->query($sql);  
 arrInserted[] = $stmt->fetchColumn();
 }

if(count($arrInserted) > 0) {
 var $arrAddValues = array();
 foreach($arrInserted as $newID) 
   $arrAddValues[] = '(' . $newID . ',' $anyBlahValue . ')';
 $sql = "INSERT INTO table2 (field1, field2) VALUES " . implode(',', $arrAddValues) . ";";
 $stmt = $dbh->prepare($sql);  
 $stmt->execute();
}

Ok, not so beautiful and untested but it should give you an idea... Watch out for query size when imploding $arrAddValues, I believe MySQL has a limit and others may too.

HTH, rash*

LAST_INSERT_ID() with multiple records insert MySQL

3 Answers