I need to insert data into 2 linked tables which have unique identifiers created automatically (set as an identity in sql server). Do i need to execute multiple statements in order to do the insert or can it be done with just one.

If i do it with multiple statements how do i return the unique id from table 1 in order to insert into table 2.

After you do the 1st insert, select @@identity (or SCOPE_IDENTITY() in SQL 2000) to get the id you need to insert in the 2nd table.

Hi

This has to be done using multiple statements. e.g.

Insert table1
values ('x', 'x')

declare @ident numeric
select @ident = IDENT_CURRENT('table1')

Insert table2
values (@ident, 'x', 'x')

The use of IDENT_CURRENT() in this situation is not wise. IDENT_CURRENT() returns the last identity value used for the indicated table across all connections. Thus, it is quite possible that you could run into concurrency issues (another user also inserts into 'Table1'). Much better to use scope_identity() here.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com