Insert in two tables simultaneously

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Insert in two tables simultaneously

Author

Topic

venkat_9099
Starting Member

8 Posts

Posted - 2004-03-15 : 04:13:18

I have a set of data that is being inserted into two tables but i want to catch the new ID (auto incremented) of the first table and place its value in a field with the second part of the data in the second table.

Thanks in advance..

Venkat

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2004-03-15 : 04:38:42

I would go for SCOPE_IDENTITY()

--Frank
http://www.insidesql.de

venkat_9099
Starting Member

8 Posts

Posted - 2004-03-15 : 04:44:00

I am using these two insert statements.. but not working

insert into login_master (user_name,password,create_date,active) values ('test','test','11/11/2004','A')

insert into customer_master (user_id,customer_name,customer_add1,customer_country) values(select @@identity,'test','test','test')

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2004-03-15 : 04:58:07

[code]
set nocount on
if object_id('a') is not null
drop table a
if object_id('b') is not null
drop table b
go
create table a (col1 int identity(1000,1), col2 char(15))
create table b (col1 int, col2 char(15))
declare @id int
insert into a (col2) values('Hello ')
insert into a (col2) values(' World')
insert into a (col2) values(':)')
--select @id = @@identity
select @id = scope_identity()
insert into b (col1,col2) values (@id, ':)')
select * from b
set nocount off

col1 col2
----------- ---------------
1002 :)

[/code]

--Frank
http://www.insidesql.de

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources