Author: Claudio Hira


How can i obtain the value of the identity column in a table, when inserting a 
record with a INSERT SQL statement.

Answer:

Found the following note when surfing several sql-dba website forums: 

You can use the SET NOCOUNT statement. SET NOCOUNT ON will prevent SQL Server from 
telling you how many rows each statement affected. SET NOCOUNT OFF will return SQL 
Server back to it's default setting. The variable @@ROWCOUNT will always contain 
the number of rows affected by the previous statement regardless of the setting of 
NOCOUNT 

do use this statement to obtain the value of the inserted id: 

whe have a table called TheTable with fields: 

Field_ID (identity column) 
Field001 (varchar) 
Field002 (int) 

Set Nocount on 
Insert TheTable (Field001, Field002) 
VALUES ('ABC', 1) 
select IdentityInsert=@@identity 
set nocount off 

Example usage: 

I have an order table with the primary order data, and a related table called 
orderdetails with the orderdetails per primary order data. The primary order table 
contains a key, field OrderID, of type identity-column autoincrement start at 0 and 
increment with 1. The table orderdetails contains the orderId as foreign key. 

Case asp e-business website: 

The visitor wants to checkout the order wich is composed. Now fire the insert sql 
to insert a record in the primary order table, using the script in this article to 
obtain the order id. With the obtained order id fire the several sql_inserts into 
the the order details table. 

Tested with sqlserver 7, ado, d5, asp