Thread: Re: getting back autonumber just inserted

I don't work with M$ DBs, but saw that "autonumber" is an M$ concept.
Purely for my own edification, how do you get the most resent value of an
autonumber in M$?  I was helping someone out who was using M$ stuff and was
amazed that there was no currval function.

Thanks,

Rick


                                    Scott Marlowe
                                                   <smarlowe@g2switchw        To:       lorid <lorid@dri.edu>
                                                                  orks.com>                  cc:
pgsql-sql@postgresql.org                                                                          Sent by:
    Subject:  Re: [SQL] getting back autonumber just inserted
pgsql-sql-owner@pos
              tgresql.org


                                                           02/03/2005 05:16 PM


                                                                                   
 




On Thu, 2005-02-03 at 16:16, lorid wrote:
> I could have sworn I kept a copy of prior emails that discussed how to
> get back a value that was just inserted into a autonumber (or in
> postgresql case a sequence number)


If you know the name of the sequence the number came from you can use
currval():

insert into table1 (info) values ('abc');
select currval('table1seq');

Assuming table1seq is the name of the sequence here.

In 8.0 there's a function to do this (I'm not sure of the name, but a
quick search of the 8.0 docs should turn it up.)

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate     subscribe-nomail command to
majordomo@postgresql.orgso that your     message can get through to the mailing list cleanly
 

> I don't work with M$ DBs, but saw that "autonumber" is an M$ concept.
> Purely for my own edification, how do you get the most resent
> value of an autonumber in M$?  I was helping someone out who
> was using M$ stuff and was amazed that there was no currval function.

I beleive they call it IDENTITY and not autonumber.
You get it using either
SELECT @@IDENTITY
or
SELECT SCOPE_IDENTITY()

depending on if you want the very latest identity or the latest int he
current scope (if you have a trigger inserting records in a different
table, they will differ - @@IDENTITY will return from the table affected
by the trigger, SCOPE_IDENTITY() will return it for the table *you*
updated)

//Magnus