SQL: is it possible to combine an INSERT and SELECT statement into one

Question

I want to add a row using SQLs INSERT statement. Is it possible that as part of this statement I can somehow get the value of the column userId which I don't update but is the AUTO_INCREMENT primary key. I need this value to update another table, however I can't follow the Insert statement immediately with a SELECT statement as there is no other unique identifier in the table on which to select.

INSERT INTO objectUrl(disp_name, loggedIn) VALUES('please change this', true)

Is it possible to get the row number (column name userId) and if so how do you do it?

Answer 1

For MySQL you have:

select last_insert_id()

Answer 2

In MySQL it's called LAST_INSERT_ID(). I believe to be technically correct, the two statements should be wrapped in a transaction so that some other INSERT doesn't mess up what ID you get back.

In SQL Sever you have IDENT_CURRENT(‘tablename’) which will only grab it from that table (still need a transaction to be safe). You could also use SCOPE_IDENTITY() which theoretically will always return the one you expect as long as you aren't doing something weird with your connection.