We help IT Professionals succeed at work.

Inserting auto id into tables when new record is inserted

Medium Priority
527 Views
Last Modified: 2012-05-06
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
Comment
Watch Question

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
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

Author

Commented:
thx but what kind of error handling...in case the id doesn't get inserted?  G
Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Thx...I have another question but will save 4 later..G
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.