Recover Generated Ids from Multiple Insert Query

Question

I want to execute a query of the form:

INSERT INTO table(field1, field2) SELECT field1, field2 FROM table WHERE id IN ( 4, 8, 15, 16, 23, 42 )

Where id is the auto_increment'ing primary key of table.

After executing this statement, I want to know what all the new id's that just got generated are. How can I recover this information? I'm interested in (maybe) doing this in a stored procedure and returning the list of newly generated id's to my application code (lets say PHP), and I would like the list to be in correlated order to what appears in the IN clause of the SELECT subquery. I'm thinking this would save me a lot of sequential INSERTs in my application code. Is that achievable?

Answer 1

I don't think so. At least not in a reliable way. LAST_INSERT_ID() will return a first auto generated id from extended insert. You could assume that all higher than this were also inserted during this insert, but this can be false in many cases (especially when there are more than one person working on a database at one time).

Answer 2

You can only get the LAST insert id BUT you can get the affected rows...

so ids will be

  $ids = range($lastID - (--$rows), $lastID);

--$rows as you want to maintain the last id too.

eg. last insert was 18 and 3 rows affected so 16,17, 18 will beyour ids but 18 - 3 would yield 15 and hence give ids 15,16,17,18 which would be wrong!

Answer 3

If you have control over the table (or can create another table) how about storing a "transaction_id?"

If you added it to your table, you could do something like this:

Declare @tranId int
Select @tranId = Max(transaction_id) + 1 from [table]
Insert Into [table] (field1, field2, transactionId)
Select field1, field2, @tranId
From //the rest of your query

You'd then pull out the new Ids:

Select Id from [table] where transaction_id = @tranId

As a note, you'd either need to run that query immediately as part of your stored procedure, or you'd need to store the transactionId you used somewhere so that you can pull out the correct rows without worrying about multiple concurrent inserts.