View source for Portable SQL/Tips/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.

'''Note:''' Be careful with more complex SELECT parts like inserting more generated values into a new record at once. In some databases these statement types are not as optimized as simple SELECTs and it may result in a lower performance than doing separate SELECT and INSERT queries.