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

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

-----------note:
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
-----------end note;

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)

--------start SQL script:
Set Nocount on
Insert TheTable (Field001, Field002)
VALUES ('ABC', 1)
select IdentityInsert=@@identity
set nocount off
----------end SQL script;

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.


enjoy...

regards
Claudio Hira (Quality-Solutions)