I know that when I'm using SQL I find myself frequently having to get my ID that I just inserted into a table, so today I'm going to sharesome of the tools I use to get the last ID I inserted into a database.  Sometimes when beginning to learn SQL you may feel the need to create a transaction and do a select for the greatest value of your primary key field to get the identity.  Although this sounds great, firstly, it requires a transaction, and secondly, it doesn't take into account non-int or non-incremented keys. 

For general database purposes in SQL Server I use the following:

view plain print about
1INSERT INTO table1 (field1, field2) VALUES ('value1','value2')
2SELECT @@IDENTITY AS last_insert_id

This simple peice of code that I use all the time.  It gets the last inserted ID on an open connection(in this case table1).  In general this will serve most purposes.  However just in case, there are alternatives.  Let's assume you are using a table(table1) which has triggers on it, and that trigger inserts a new record into another table(table 2) when running.  In that case the code above would return the ID of the inserted record in table2.  The reason for this is that the above code doesn't take into account scope.  The following code will return the last ID with reference to scope of the active connection.

view plain print about
1INSERT INTO table1 (field1, field2) VALUES ('value1','value2')
2SELECT scope_identity() AS last_insert_id

This code will grab the last ID of table1 regardless of triggers. 

I hope these two tools will be as useful to you as they have been to me.  Enjoy!

This entry was posted on October 13, 2009 at 3:35 PM and has received 507 views. There are currently 1 comments. Print this entry.