Best Practice for retrieving last record

Hi there, very sorry if this is the wrong forum to post this in.

I want to know what is the BEST practice, the correct Microsoft way of doing this:

basically, lets say I am inserting a new record into SQL. simple customer record:

firstname

lastname

address

city

postcode

password

dateOfRegistration (SQL has this value and the default value is the getdate())

That's all very well. I want to know how I can get the recordID for this and return that back from the caller (returning is easily done) -

You cannot really after this insertion, perform a SELECT statement to get the LAST record entered, as there maybe several records that could all be inserted at the same time by coincidence. It's not the best way of going about this.

I want to know what is the best way of getting the just inserted record's recordID - I was thinking about using date and time, manually inputting them and then using that to retrieve the last record/current inserting record but again its not the best way of going about doing this.

what is the best way?

Many thanks for your help!

Hi,

If your recordID is an Identity column of your table, you could use the SCOPE_IDENTITY function.

INSERT INTO Customer (Fname,Lname,Address,City,PostCode,PWD,DOfReg)

VALUES ('John','Smith','123 drwerwr','Montreal','X1X 1X1''***','2006-03-31')

SELECT SCOPE_IDENTITY()

This will retreive the last RecordId inserted

|||

cool - had no idea such a thing existed but then again i am still learning the great SQL Server

so please tell me technically, how does the SCOPE_IDENTITY() work? in technical terms - what does it do in process?

|||

From BOL

SCOPE_IDENTITY and @.@.IDENTITY return the last identity values that are generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @.@.IDENTITY is not limited to a specific scope.

HTH,

Eric

|||you can add a timestamp field to your table then order by it desc and select the top 1|||

Don't know about using a time stamp ...

The timestamp value would change on update as well.. and in a multi-user application you can't garantee that the last record inserted is yours if you use a timestamp.

|||Hi,

if you are using SQL Server 2005 you can use the new keyword OUTPUT within the insert command. By the new keyword you can directly get a value back which can represent the identity or whatever column:

Look for the output clause in the BOL: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/41b9962c-0c71-4227-80a0-08fdc19f5fe4.htm

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

I am using SQL Server 2000 but also interested in the cool stuff of SQL Server 2005 (on the side)

Currently using the SCOPE_IDENTITY() in SQL Server 2000

the suggestion about sorting the results in ASC order and select the last one - well, it may be logical to do so but too much effort in terms of performance and ineffeciency :-)

any other best way? :-)

|||

Using identity values for PRIMARY KEY values is fine, and a practice I use all of the time. But, it should not be your only unique criteria on the table.

For example, consider your table:

firstname, lastname, address, city, postcode, password, dateOfRegistration

What if two rows were inserted with the same firstname, lastname, address, city, postcode, password, dateOfRegistration. Most likely this would be in error right? Well if haven't added a UNIQUE constraint to the data, what happens when you look them up? How will you as a human tell them apart, much less the stupid, just do what you programmed it to computer.

So at the very least add a UNIQUE constraint on all of these columns.

Then, if you don't have SCOPE_IDENTITY() (An instead of trigger will invalidate SCOPE_IDENTITY() if you ever need one, for example) you just just say:

select rowId
from tablename
where firstname = @.firstname
and lastname = @.lastname

etc. Don't give up all control of the data/situation to SQL Server, though use the tools like scope_identity to make life easier. :)

|||

Many thanks Louis i appreciate this

Well the unique check would be the address in my case which the stored procedure checks to see if it exists, if it does it returns a value (like -1 or whatever) otherwise it continues and returns the ID of the record which is inserting.

Thank-you!

|||If you had the unique constraint there it would probably make this operation faster too.|||

awesome!!!!!

Performance is the key baby, LOVE SQL

thank-you Louis :-D