SQL statement - retrieve id of last record added

[BiggerBrother]

After adding a record to a db, I want to retrieve the autonumber field. I realise that the record will be the last in the list, however, if two records should happen to be inserted at the same time, this wouldn't work.

Is there a way with SQL to retrieve the whole record once it has been inserted?

Many thanks

BB

 

[RoyVidar]

Depending on database you are using, and what you develop with.

For SQL server and Access, if you issue a "select @@identity" on the connection you inserted the record with, you should get the latest autonumber, which you could use to retreive the whole record.

I'm not very versed on SQL server, but I think if you have a trigger performing other updates/insert based on it, you should try scope_identity. Here's a short thread on the issue thread183-721955

Perhaps consider posting in a forum dedicated to the product(s) you are using, if you need more specific answers.

Roy-Vidar

 

[BiggerBrother]

Thanks for the tip. I was hoping that there was a cross db solution, as the application will work with more than 1, and I don't really want tp have to have too many different sql statements for different procedures.

Thnaks for the advice any way.

BB

 

[r937]

there is a cross-database solution

it comes from realizing that an autonumber is just a surrogate key

the table should have one or more additional columns that will be unique, and uniqueness is what you're after

insert the row, then query it back using the same values of this unique key that you just used to insert the row

if you say "but my table doesn't have any other column or columns in it that are unique" then there may be some problem with your design

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[BiggerBrother]

I completely agree, BUT i was interested to see if there was a way to retrieve the autonumber field without creating another query?

A check is made before the data is inserted to ensure the user isn't duplicating another record, however with my limited knowledge of SQL i wanted to check that I was going about this the right way.

Many thanks

BB

 

[r937]

retrieve the autonumber value without running another query?

no, no consistent way across databases

in mysql, use the last_insert_id function
in sql server, use the @@identity function
et cetera

and you're already doing an unnecessary extra query if you have to check before inserting!

the purpose of a unique constraint is to prevent duplicates, so you should just go ahead and insert, then trap the error message that you get if the insert was a duplicate

to recap:

1. insert record
2. if no error, select record to get autonumber


rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[RiverGuy]

r937, why the need for another unique column(s)? Take a sales transaction. You may have one store, with 2 cash registers, without customer accounts. Therefore, a sales transaction table may have the fields: TransactionNumber, Date, Payment type, TenderedAmount. It would be very easy to have 2 sales at the same time with the same payment types. The only way to differentiate between the 2 transactions without the TransactionNumber as an Identity field would be by the transaction's Transaction Details--and that is not even 100%, as one could have 2 sales involving the same item.

As the methods accross databases likely would not be that involved, it shouldn't be a problem to send a slightly different sql statement depending on the database. Also, referring to @@IDENTITY, be aware of SCOPE_IDENTITY() as well, as they can return different results in SQL Server.

 

[SQLSister]

I have to tell you to never, ever use @@identity in SQL Server, use scope_identity() instead. The reason is the @@identity will return the the incorrect value if you have a trigger on a table that inserts to another table with an identity field. It will return the value for the table inthe trigger not the one you just inserted to. Since you can never know when a trigger may be put on a table, to use @@identity is to ask for data integrity problems. @@identity is a very dangerous thing to use.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[BiggerBrother]

Ah well, it looks like I may have to bit the bullet here then and write database specific sql command, and a have done with it.

Many thanks for all your advice.

BB

 

[r937]

riverguy, without getting into a big discussion, which you might not want, i wasn't suggesting another unique key, i was suggesting that the table should already have one (besides the autonumber, of course)

and yes, i am aware of the sales transaction problem ;-)

do a google for "three cans of cat food" +sql

some would argue that you don't really have a relational table if it doesn't have a candidate key, but if this is the issue, i think i'm on the side of the database practitioners (who say that yes of course you can have a table without a unique key) rather than the relational theorists (who say that every relation must have a candidate key)

so in the case of the cat food, yes, you'd better declare some kind of surrogate primary key

so i guess the "read the row back using the candidate key values" won't work for the cat food, and i stand corrected

(but aren't cat-food-type sales transaction records typically loaded in bulk, not inserted and queried one at a time? and let's not forget the main reason for wanting to know the autonumber key value -- which is to use as a foreign key value in some related table, which is unlikely for individual rows of cat food)

about @@identity, yes, i know about scope_identity, it just slipped by mind (again)

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[RiverGuy]

r937,
I will take a look at that Google search. I wasn't wanting to get into a big discussion either. I am just USUALLY of the opinion that the outcome is more important than the means, provided the means work effeciently and effectively. By this, I mean that a system's speed and/or functionality should not be inhibited ONLY to comply for the current industry accept normal-form. It should, however, be comprised if not adhering to some relational principles will have adverse effect on the system. And I tend to look at databases in the scope of a system, and not as islands unto themselves.