sql - Whats the standard way of getting the last insert id? -

what's sql standard last inserted id? if there such thing.

mysql: last_insert_id()
postgresql: ... returning f_id
mssql: scope_identity()
... more examples here ...

i mean, databases have different implementations that, there isn't standard such common task?

see answer retrieve inserted row id in sql

in short, there no cross database way this, except max(id) - not guaranteed result , has many many pitfalls, e.g.

• other inserts can come between last insert , max query
• cannot used high transaction tables (max issue read lock, rdbms-specific methods not read table)

the ansi standard relates identity/autonumber/auto_increment/sequences first appeared in sql:2003 awaiting implementation major rdbms. resemble oracle/postgresql sequences.

the sql:2003 standard makes minor modifications parts of sql:1999 (also known sql3), , officially introduces few new features such as:

- sequence generator, allows standardized sequences

another change in sql:2003 output using clause there little information it. sybase , sql server have done different things it, unclear yet how pan out. sql server implements as

insert tbl(..) output inserted.identity_col @sometablevar values(..)