[thelist] @@IDENTITY w/SQL 7 - ColdFusion query

[rudy]

erik, there's nobody home on weekends   ;o)

i don't understand how @@identity works, only that it does

i'm sure anthony will jump in here when he sees this

oh, and using the cftransaction block is not wrong if it works

plus, it's safe, eh?

your first way might also be okay -- try it again with a semicolon
separating the two sql statements

;o)


by the way, what you're running up against, the requirement to know the
primary key after you insert a row, because you are about to insert a bunch
of child rows in another table using the primary key value as the foreign
keys of the child rows, well, that's a very common situation

[ rudy grabs a chance to build up his tip reserves a bit,
  for the day -- and you know it's coming -- when he again
  says something really stupid aand needs to pay a tip for it ]

<tip type="querying back the primary key">

having a function like @@identity is certainly the right way to go, but
what if you don't have that luxury?  e.g. on a different database system?

if the primary key is an autonumber or identity or sequence field, you
could always just select max(id) but -- CAUTION -- keep in mind you *must*
wrap the insert / select max(id) in a transaction block or you run the risk
of overlapping inserts selecting max(id) as the same value...

but what if the primary key is not incremental, i.e. what if you cannot use
max(id)?

answer:  you can still re-query the inserted record using as many
identifying fields as necessary to ensure uniqueness

best example i can think of is where you are adding people

typically, you have

   id
   lastname
   middleinit
   firstname

now if you just accept input blindly, then shirley you will one day end up
with two rows with different primary key ids but the other values being
Neuman E Alfred

if you do *not* blindly add rows, and instead try to catch situations like
two rows with the same multiple columns (lastname, middleinit, firstname),
then you can do so either before or while (see below) inserting the parent
row

but no matter how you control it, you will also have the values of the name
fields in a variable somewhere, right?

so you can get the value of the just-assigned id by querying the row with
the name fields you just inserted

this might seem like a trivial distinction, querying back the primary key
using other columns rather than max(id), but you have to do it this way if
the id is not assigned incrementally

and there is also a good reason to do it this way even if max(id) wouyld
work -- querying back the inserted row this way can be done asynchronously!
no transaction block!  no locking!  no bottleneck!  no slowdown!  look ma,
no angry server admins at my cube door!!!

now, i am not saying this way is going to be faster than @@identity, which,
after all, is a special database function designed just for the purpose
illustrated

but it works, and no transaction block is needed

for those of you interested in why it works, it all depends on the
uniqueness of records

in the example above, the three name fields form the "real" primary key,
and the numeric id is a "surrogate" key

when you do the insert, and prevent double rows on the name, either before
or during the insert, you are in effect ensuring the uniqueness of the
"real" primary key

in a real life example there would be other fields to guarantee uniqueness,
like social insurance number, since in real life there *are* people with
the same name

anyhow, there are several ways to prevent duplication, but the best way is
to do it with a uniqueness constraint defined in the database on the "real"
primary key column(s) -- just go ahead and insert, and the database will
tell you in an error message if it's a duplicate

then after the insert, query the row back using the "real" key values

tada

yes, that's two calls to the database (one query each)

and yes, that might not be as efficient as a transaction block (one call
containing two queries)

but if you are in a real high-volume situation (thousands of database calls
per second) where the distinction matters, then you will probably have to
get your dba's approval no matter which way you code it, right dex?

</tip>

rudy
http://rudy.ca/