Click here to Skip to main content
15,943,233 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
We need to capture the auto-incremented value that we get when we insert into the first table. how we capture it, we need to use it to insert into the second table in sql server.
Posted
Updated 15-Oct-11 0:21am
v2

Have a look at this MSDN article: Retrieving Identity or Autonumber Values[^].

As states in the article you should probably use SCOPE_IDENTITY to get the auto number of the last insert within the current execution scope.
 
Share this answer
 
v2
Comments
Espen Harlinn 15-Oct-11 6:37am    
Excellent
André Kraak 15-Oct-11 6:42am    
Thanks.
Abhinav S 15-Oct-11 6:42am    
My 5. Almost similar answer to mine but with additional information about SCOPE_IDENTITY.
André Kraak 15-Oct-11 6:59am    
Thanks.
 
Share this answer
 
Comments
Espen Harlinn 15-Oct-11 6:38am    
Excellent
Abhinav S 15-Oct-11 6:41am    
Thank you.
André Kraak 15-Oct-11 6:59am    
My 5.
Abhinav S 15-Oct-11 8:12am    
Thank you.
Use the OUTPUT Clause[^] - you'll find examples on how to use this at the bottom of the page.

Input and Output Parameters, and Return Values[^] shows you how to access the output from .Net

[Update]
If you follow this approach you don not need to implement stored procedures to get the job done.

Best regards
Espen Harlinn
 
Share this answer
 
v2
Comments
Abhinav S 15-Oct-11 6:41am    
My 5!
Espen Harlinn 15-Oct-11 6:46am    
Thank you, Abhinav!
André Kraak 15-Oct-11 6:59am    
My 5.
Espen Harlinn 15-Oct-11 7:26am    
Thank you, André!
SQL
select max(id) from table1.

store result of query in any variable.
C#
int id=result of query

Now if you want to insert data in both tables at the same time then

to insert id in second table you can use

now insert (id+1) for table2.
your table2 id column should not be auto increment.
hope it will helps you.

Note:This works in case of single user desktop application
 
Share this answer
 
v2
Comments
Espen Harlinn 15-Oct-11 6:37am    
This approach should not be used as it's likely to fail in a multiuser scenario
uspatel 15-Oct-11 7:22am    
Thanks for comments
Pritesh Aryan 15-Oct-11 8:49am    
ya this is the problem my friend have faced some days ago.
nice comment..
PATEL RAJEEV 15-Oct-11 7:15am    
thanks

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900