Articles   Members Online:
-Article/Tip Search
-News Group Search over 21 Million news group articles.
-Delphi/Pascal
-CBuilder/C++
-C#Builder/C#
-JBuilder/Java
-Kylix
Member Area
-Home
-Account Center
-Top 10 NEW!!
-Submit Article/Tip
-Forums Upgraded!!
-My Articles
-Edit Information
-Login/Logout
-Become a Member
-Why sign up!
-Newsletter
-Chat Online!
-Indexes NEW!!
Employment
-Build your resume
-Find a job
-Post a job
-Resume Search
Contacts
-Contacts
-Feedbacks
-Link to us
-Privacy/Disclaimer
Embarcadero
Visit Embarcadero
Embarcadero Community
JEDI
Links
How to return identity id from insert_SQL Turn on/off line numbers in source code. Switch to Orginial background IDE or DSP color Comment or reply to this aritlce/tip for discussion. Bookmark this article to my favorite article(s). Print this article
17-Jul-03
Category
Database-SQL
Language
Delphi 2.x
Views
201
User Rating
No Votes
# Votes
0
Replies
0
Publisher:
DSP, Administrator
Reference URL:
DKB
			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

			
Vote: How useful do you find this Article/Tip?
Bad Excellent
1 2 3 4 5 6 7 8 9 10

 

Advertisement
Share this page
Advertisement
Download from Google

Copyright © Mendozi Enterprises LLC