[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

insert in master child table without cursors

Posted on 2011-10-13
8
Medium Priority
?
196 Views
Last Modified: 2012-08-14
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
Comment
Question by:sanagarwl
  • 4
  • 4
8 Comments
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 36964450
declare @ID int

insert into mastertable (col1)
select col1

select @ID = SCOPE_IDENTITY()

insert into childtable(id, col2, col3)
select @ID, col1, col2
0
 

Author Comment

by:sanagarwl
ID: 36964511
but, select col1, col2, col3 from abc returns multiple records...
0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 36964941
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 36964955
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
 

Author Comment

by:sanagarwl
ID: 36970055
thanks ewangoya:

one quick question - so we do have to use cursors, correct?
0
 
LVL 32

Accepted Solution

by:
Ephraim Wangoya earned 2000 total points
ID: 36970127

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
 

Author Comment

by:sanagarwl
ID: 36970402
thanks a lot - how do I order the query then?
0
 

Author Comment

by:sanagarwl
ID: 36970477
ordered it on Id - thanks a lot for the help
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question