How to get inserted row id with TSQL

[Risord]

I am doing data insert but hit a wall since so far I know there is no possibility to return inserted id with TSQL. My data is quite relation heavy and single unit of data may not be unique by itself but its about relationships. Also so far I know you cannot insert partial graphs at once.

So plan is:

1. Insert Car (which doesn't have any unique data per se)
2. Insert Wheels by CarID... But since we don't know id and we cannot identify our car... we are in trouble.

Only workaround I can imagine is simply opt-out from auto increment identifiers and use client side generated id (guid maybe) instead. Honestly I cannot even imagine almost any practical use case where id return wouldn't be necessary. How you people can live without this or is it just my workflow? 😄

[rspeele]

Hi Risord,

Try using scope_identity() like this.

In the future I might add some cross-database way of getting the inserted ID that would take advantage of the various "output" clauses on some DBs.