Friday, June 22, 2007

SQL Server 2000 - Get Most Recent Record ID

If you're like me, you've probably wanted to get the record id of the record you just inserted. There are a few different methods of doing so depending on what database you're using, but this one is specific to SQL Server 2000. You'll have to try it yourself to see if it works on other databases. I THINK it works on Access 2000 or higher, but didn't test it. Should also work on SQL Server 2005 and 2005 Express Edition.

When you call a SQL statement, you can call multiple statements separated by a semicolon. The following is an example of a SQL statement that inserts a new record and then gets the ID assigned to that record, be it an autonumber or guid:
INSERT INTO TheTable (Field1, Field2) VALUES ('Value1','Value2'); SELECT @@IDENTITY AS NewestID
An example of how to use this would be (this assumes you've already set up your database connection and named it dbConn):
rID = ""
strSQL = ""

strSQL = "INSERT INTO TheTable (Field1, Field2) VALUES ('Value1','Value2'); SELECT @@IDENTITY AS NewestID"
set rs = dbConn.Execute(strSQL)
rID = rs("NewestID")
Now you have the record ID of your newest record in a variable!

2 comments:

Carl Grint said...

It defiantly works in Access 2000, as I use this all the time when using Access.

In SQL server I tend to use a Stored Procedure to cut down on the server requirements. :o)

Anonymous said...

doesn't appear to work on SQL2005