Inserting auto id into tables when new record is inserted

I have a tbl_test that has an identity field; this field is used in all other tables...when a user inserts new information, I need all other tables to have the new ID inserted as well.

@test
@test1
@test2
@test3

begin

insert into table1 (test1) values (@test1)
@@SCOPE IDENTITY
insert into table2 (id, test2) values (@test2)
ETC


how do you write this code?

Thx
Glen_DAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
like this:

declare @id int
declare @err int
begin transaction
 
insert into table1 (test1) values (@test1)
 
set @err = @@ERROR, @id = SCOPE_IDENTITY()
 
if @err = 0
begin
  insert into table2 (id, test2) values (@id, @test2)
  set @err = 0
end
 
if @err = 0
begin
  insert into table3 (id, test3) values (@id, @test3)
  set @err = 0
end
 
if @err = 0
begin
  commit 
end
else
begin
  rollback
  raiserror('insert failed', 16,1)
end

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this will do:
declare @id int
insert into table1 (test1) values (@test1)
set @id = SCOPE IDENTITY()
insert into table2 (id, test2) values (@id, @test2)
insert into table3 (id, test3) values (@id, @test3)
... etc .. 
note: you will need error handling ...

Open in new window

0
 
Glen_DAuthor Commented:
thx but what kind of error handling...in case the id doesn't get inserted?  G
0
 
Glen_DAuthor Commented:
Thx...I have another question but will save 4 later..G
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.