Can you combine INSERT INTO and Last_Insert_ID in one SQL Statement

Question

I'm need to get the ID of the last record inserted into my table.

INSERT INTO mytable (Column1, Column2) VALUE ('Test', 'Bob');
SELECT/SET LAST_INSERT_ID() as NewID;

Response.Write rst("NewID") '(for example)

Can it run in one statement, or do I need to run the SELECT LAST_INSERT_ID after the INSERT SQL has ran.

I am using MYSQL and ASP

If the insert is on a different table then you can use trigger if needs to be done via sql else using application level it could be done pretty easily. — Abhik Chakraborty, May 28 '15 at 12:37
possible duplicate of [LAST\_INSERT\_ID() MySQL](http://stackoverflow.com/questions/3837990/last-insert-id-mysql) — user692942, May 28 '15 at 14:18

score 1 · Answer 1 · 2015-05-28T12:57:21.837

1

You need to run it after every statment as it will always returns one id of most recent inserted record.

To achieve your goal, You need to do is, whenever any record inserted to table, get the last id and store it in to any string/table every time and return it from your sproc. This way you get all inserted id at once.

edited May 28 '15 at 12:57

answered May 28 '15 at 12:44

Isn't that what the OP is trying to avoid in the first place? Also this is SQL Server **not** MySQL - *"I am using MYSQL and ASP"* – user692942 May 28 '15 at 12:56
1

I don't have any problems running the "SELECT LAST_INSERT_ID() as NewID", works every time running as two separate statements. I'm just concerned about accuracy, what happens if a new record is inserted bewteen the INSERT INTO and SELECT LAST_INSERT_ID(), the odds must be a billion to one, but is it possible. – John Moore May 28 '15 at 13:10
Not sure this is up-vote worthy you've just edited your [original answer](http://stackoverflow.com/revisions/30506961/1) based off what others like BabyDuck have already pointed out. The first answer completely missed the point, this however is closer but not great *(no real explanation)*. – user692942 May 28 '15 at 14:13

score 0 · Answer 2 · answered May 28 '15 at 17:27

0

I think that you need to run 2 procedure, first insert and then select order by id desc

answered May 28 '15 at 17:27

Fabio Pellerito

204
1
9

Can you combine INSERT INTO and Last_Insert_ID in one SQL Statement

2 Answers2