Portable SQL/Tips/Handling generated IDs

Handling generated IDs

If a new record is inserted into a table which contains a column with an automatically generated ID, you usually need that ID to insert a depending record into another table. Unfortunately, there is no standard how to retrieve such values and all database vendors provide their own statements or functions.

The good thing is that most of the time you don't really need to know that value - it's only required for inserting the dependent record. Therefore, you can use a variant of the INSERT statement to lookup that value and insert it along with the other values.

INSERT INTO table1 (idcol, value1, value2 )
SELECT id, 'test', 'value'
 FROM table2
 WHERE somecol = 'oldvalue'

Instead of selecting the generated ID value before executing a plain INSERT statement, you can do it in a single statement which is supported by almost all SQL databases.