0

Inserting multiple records to a table in MYSQL returns me last inserted ID.

Is there a way I can get the list of all inserted records ?

One way would be to insert each record and then fetch that record using last inserted ID. But obviously that will raise performance issues.

laxman
  • 1,781
  • 4
  • 14
  • 32
  • The only way I can think of doing this is to save all the old IDs in a temporary table, insert the new records, then return all the IDs that don't exist in the temp table. – Barmar Aug 13 '20 at 23:26
  • Does your table have a `creation_time` column? You can save the time when you started inserting, then select all the records added on or after that time. – Barmar Aug 13 '20 at 23:27
  • I'm not sure that using several single row insert statements vs a single multiple rows insert statement is really that much of a performance hit, so long as you don't separately submit each single row insert statement to your database. This way each single row insert can be followed by a statement to get each insert's record id created by that insert. However, you're going to have to collect the different row ids in an array or string and send that back to whatever code that initiated the inserts, which other than using a select from dual statement, I don't know how you might do. –  Aug 14 '20 at 02:48
  • @Barmar I don't have list of old ids, because I want to create multiple records. As you mentioned, I also got the idea of inserting records and then sorting by creation_time and limiting it to the number of records requested. But I eventually ended up adding a query param to my POST request to limit the records. Thanks for your time though. – laxman Aug 14 '20 at 07:30
  • 1
    My idea was that you would make the list of old IDs before the insert: `CREATE TEMPORARY TABLE old_ids AS SELECT id FROM yourTable`. Then do all the inserts, and you can get all the new IDs with `SELECT id FROM yourTable WHERE id NOT IN (SELECT id FROM old_ids)` – Barmar Aug 14 '20 at 14:22
  • just realised my idea of implementing it would had caused me a lot of problems. :( – laxman Aug 17 '20 at 04:42

0 Answers0