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.

Using the @@identity column will help you out.
Try
http://msdn.microsoft.com/en-us/library/aa933196%28v=sql.80%29.aspx[^]
http://msdn.microsoft.com/en-us/library/ms187342.aspx[^]

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

select max(id) from table1.

store result of query in any variable.

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