This will return a record set with one row and a column of ID with the value of the id that was inserted.
INSERT INTO TableName (Field1, Field2, FieldN) VALUES ('Value1', 'Value2', 'ValueN')
SELECT @@IDENTITY as ID
Comments: Flat Nested Threaded Embedded Oldest First Newest First Casting identity By ? on Thursday, October 23, 2003 (EST) Don't you have to cast the identity to int ?Select cast( @@identity as int) as ID btw, cool site. What portal system are you using ? Reply to this Comment Warning By ? on Monday, November 10, 2003 (EST) This works well (and I've been using this for ages) unless you're using triggers. Have a look at this: http://weblogs.sqlteam.com/travisl/posts/405.aspx Reply to this Comment If using SQL 2000 By ? on Wednesday, October 06, 2004 (EST) If youa re using SQL 2000 you are going to want to use the new SCOPE_IDENTITY() function. The reason being is that @@IDENTITY will retun you the last IDENTITY value inserted into a table. This means that if you have triggers that run on insert and put rows into other tables, the identity value that you are going to get will be from that table, not the one you intended. SELECT SCOPE_IDENTITY() as ID Reply to this Comment qweasdqweas By ? on Monday, March 28, 2005 (EST) asdasdqweasdweasdsdf dfsdfqwasd asd awedasdasd asdqwas adasdqwe asd sdasd adawdsdasd ascasd Reply to this Comment qqqqq By ? on Monday, March 28, 2005 (EST) Reply to this Comment
Don't you have to cast the identity to int ?
Select cast( @@identity as int) as ID
btw, cool site. What portal system are you using ?
Reply to this Comment
If youa re using SQL 2000 you are going to want to use the new SCOPE_IDENTITY() function. The reason being is that @@IDENTITY will retun you the last IDENTITY value inserted into a table. This means that if you have triggers that run on insert and put rows into other tables, the identity value that you are going to get will be from that table, not the one you intended.
SELECT SCOPE_IDENTITY() as ID