Stored Procedure for insert into related tables

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 2005 Forums

Transact-SQL (2005)

Stored Procedure for insert into related tables

Author

Topic

mody82
Starting Member

13 Posts

Posted - 2009-06-11 : 05:25:18

Hi everyone ,

I want a procedure for inserting data into related tables means one is the primary table and the other is forigen,

Ex:
I have two tables , Branch and BranchDetails.
Branch Columns:
-BranchID (Primary Key)
-BranchName
BranchDetails Columns :
-BranchID (Foriegn Key)
-BranchAddress
-ContactPerson

So friends , How can I create the procedure to insert values throw the procedure.

Waiting your answers
Best Regards

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-06-11 : 05:29:15

first u hav to insert the data into Branch table, then u got the output value ( branchid)
so, now insert that output value(branchid) into BranchDetails table

mody82
Starting Member

13 Posts

Posted - 2009-06-11 : 05:32:26

First of all thanks for your fast reply,
and I request u to give an example to be more clear for me .

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-06-11 : 05:45:22

declare @id int
insert into branch select 1,'eee'
select @id = scope_identity()
insert into branchdetails select @id,'india','kkk'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-11 : 11:26:41

quote:
Originally posted by mody82

First of all thanks for your fast reply,
and I request u to give an example to be more clear for me .

Will you be interested in row by row insert or batch insert?

mody82
Starting Member

13 Posts

Posted - 2009-06-13 : 03:29:07

quote:
Originally posted by bklr

declare @id int
insert into branch select 1,'eee'
select @id = scope_identity()
insert into branchdetails select @id,'india','kkk'

Hi bklr
I tried your answer ,it's working for the first table , but for the second table it will not insert the @Id value , it will insert the 'India', 'KKK' only.

is there any other solution ???
thx in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-14 : 01:56:39

quote:
Originally posted by mody82

quote:
Originally posted by bklr

declare @id int
insert into branch select 1,'eee'
select @id = scope_identity()
insert into branchdetails select @id,'india','kkk'

Hi bklr
I tried your answer ,it's working for the first table , but for the second table it will not insert the @Id value , it will insert the 'India', 'KKK' only.

is there any other solution ???
thx in advance

I'm repeating my earlier question.

are you doing row by row insert or batch insert?

mody82
Starting Member

13 Posts

Posted - 2009-06-14 : 03:02:06

quote:
Originally posted by visakh16

quote:
Originally posted by mody82

quote:
Originally posted by bklr

declare @id int
insert into branch select 1,'eee'
select @id = scope_identity()
insert into branchdetails select @id,'india','kkk'

Hi bklr
I tried your answer ,it's working for the first table , but for the second table it will not insert the @Id value , it will insert the 'India', 'KKK' only.

is there any other solution ???
thx in advance

I'm repeating my earlier question.

are you doing row by row insert or batch insert?

Hi Visakh ,
Actually I want to insert in batch way not row by row,
so plz give me your answer with example
best regards

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-14 : 03:11:39

in that case you cant use variable to get generated id as it can hold only a single value. what you need is this


declare @inserted_values table
(
id int,
othercols....
)

 insert into branch 
ouput inserted.idcol,inserted.other values... into @inserted_values
select values....
from table1 
join table2 ....


insert into branchdetails 
select id,...
from @inserted_values

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources