• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 198
  • Last Modified:

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.
0
sanagarwl
Asked:
sanagarwl
  • 4
  • 4
1 Solution
 
Ephraim WangoyaCommented:
declare @ID int

insert into mastertable (col1)
select col1

select @ID = SCOPE_IDENTITY()

insert into childtable(id, col2, col3)
select @ID, col1, col2
0
 
sanagarwlAuthor Commented:
but, select col1, col2, col3 from abc returns multiple records...
0
 
Ephraim WangoyaCommented:
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;

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Ephraim WangoyaCommented:
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;

Open in new window

0
 
sanagarwlAuthor Commented:
thanks ewangoya:

one quick question - so we do have to use cursors, correct?
0
 
Ephraim WangoyaCommented:

I'm not actually using a cursor

"while exists(select 1 from abc where id >= @currentrecord) do"

I just test to see whether I have gone through all the records using the @currentrecord variable.

The query to get the records should actually be ordered, otherwise you can end up skipping some records
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
  order by id 

  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;

Open in new window

0
 
sanagarwlAuthor Commented:
thanks a lot - how do I order the query then?
0
 
sanagarwlAuthor Commented:
ordered it on Id - thanks a lot for the help
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.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now