sanagarwl
asked on
insert in master child table without cursors
I have a select query.
and, need to insert results from the select query above into 2 tables that have a
master child relationship. Is there a way to do this without cursors such that once
I insert in master table I can insert in child table and use the primary key in master table to into the
fk in the child table. please note the primary key in the master table is an identity column.
so,
select col1, col2, col3
from abc
stmt 1) insert col1 in master table
stmt 2) insert col2, col3 in child table
use primary key generated in stmt 1 to be used in fk in stmt 2.
if 10 records are generated in select stmt then 10 records go into master table and 10 go into
child table so 1-1 relationship between master child tables.
and, need to insert results from the select query above into 2 tables that have a
master child relationship. Is there a way to do this without cursors such that once
I insert in master table I can insert in child table and use the primary key in master table to into the
fk in the child table. please note the primary key in the master table is an identity column.
so,
select col1, col2, col3
from abc
stmt 1) insert col1 in master table
stmt 2) insert col2, col3 in child table
use primary key generated in stmt 1 to be used in fk in stmt 2.
if 10 records are generated in select stmt then 10 records go into master table and 10 go into
child table so 1-1 relationship between master child tables.
ASKER
but, select col1, col2, col3 from abc returns multiple records...
You just need to create a loop, for that for example
declare @currerecord int, @ID int, @col1 int, @col2 int, @col3 int
set @currentrecord = 0
while exists(select 1 from abc where id >= @currentrecord) do
begin
select top 1 @currentrecord = id,
@col1 = col1,
@col2 = col2
@col3 = col3
from abc
where id >= currentrecord
set @currentrecord = @currentrecord + 1
insert into mastertable (col1)
select col1
select @ID = SCOPE_IDENTITY()
insert into childtable(id, col2, col3)
select @ID, col1, col2
end;
Sorry, here
declare @currerecord int, @ID int, @col1 int, @col2 int, @col3 int
set @currentrecord = 0
while exists(select 1 from abc where id >= @currentrecord) do
begin
select top 1 @currentrecord = id,
@col1 = col1,
@col2 = col2,
@col3 = col3
from abc
where id >= currentrecord
set @currentrecord = @currentrecord + 1
insert into mastertable (col1)
select @col1
select @ID = SCOPE_IDENTITY()
insert into childtable(id, col2, col3)
select @ID, @col1, @col2
end;ASKER
thanks ewangoya:
one quick question - so we do have to use cursors, correct?
one quick question - so we do have to use cursors, correct?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks a lot - how do I order the query then?
ASKER
ordered it on Id - thanks a lot for the help
insert into mastertable (col1)
select col1
select @ID = SCOPE_IDENTITY()
insert into childtable(id, col2, col3)
select @ID, col1, col2