[thelist] Free Tip about SQL

[Jason Handby]

> A colleague of mine came up to me and asked:
> "Is there a way, in a SQL statement, to return the auto generated ID of a
> record when it's created by an insert values query? ..."
>
> I answered:
> "Add another column in your table for a timestamp varchar(24) variable.
> Store that number as a variable. Then at your leisure call for a row that
> contains your newly added timestamp variable and you'll have the new ID.
>
> The likelihood of two functions executing/generating two rows at the same
> time (seconds) is probable. I used a self-developed function to get
> milliseconds or micro time, which decreased the probability to about
> 1:1,000,000 chance I'll duplicate the record calling procedure.

A much, *much* better idea is to use the built-in functionality that your
database provides for getting the last automatically-generated ID. In SQL
Server this is the @@IDENTITY system variable (there are a couple of other
ways in SQL Server 2000). In MySQL you can use LAST_INSERT_ID().

Really, truly, honestly, this is a much much *much* better and more
efficient way of doing it -- and it's absolutely guaranteed to work, even if
two people add records at exactly the same time...



Jason