p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   SQL Server 2000 (http://p2p.wrox.com/forumdisplay.php?f=20)
-   -   inserting into joined tables (http://p2p.wrox.com/showthread.php?t=1981)

cjennings July 23rd, 2003 04:47 AM

inserting into joined tables
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.

pgtips July 23rd, 2003 04:59 AM

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.

nbryson July 23rd, 2003 05:02 AM


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')

Jeff Mason July 23rd, 2003 06:10 AM

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.

All times are GMT -4. The time now is 03:52 AM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.