June 2, 2009 at 3:14 am
Hello everyone,
This is the first time im doing bulk insert operation. Im aware of BCP, BULK INSERT and OPENROWSET OPTION. unfortunately I lack idea of using it for multiple tables insertion. There exhibit foreign key relation and that I need to insert identity generated on parent to child. Having had this requirement Im not able to use any of the options above and ended up using Insert Into with select combo. It took nearly 2 hours and 40 mins to insert the data. Im worried that this will greatly impact on future when we are going on live system. During research I found that many people are advised to use set - based approach. I assume using JOIN would produce set-based operation but im also concerned whether usage of where condition will still prompt row by row indexing.
My sample table and querys are below . any help in right direction will help me improve my SQL knowledge.
table structure (example structure )
Table parent
(
id int identity (1,1),
name varchar(20),
city varchar(20),
zipcode varchar(20),
p_id int not null
)
Table child
(
code int identity (1,1) not null,
child_id int not null,
country varchar(20),
region varchar(20),
CONSTRAINT FK_ID FOREIGn KEY (child_id) REFERENCES Parent(id),
CONSTRAINT PK_Code PRIMARY KEY (code)
)ON [PRIMARY]
There exhibit 1 - one or more (p) relationship between parent and child
Query (*part of the code is left for clarity)
First I create a temp table to hold the data that satisfy the where condition
INSERT INTO #tmpP
select x.p_id, x.col1,x.col2,x.col3 from x where x.colm4 <> 'N'
-- now inserting in to final table inside cursor
declare @pidentity int
set @pidentity = 0
DECLARE cur cursdor for
select id from x
open cur
fetch next from cur into @tmpID
while (@@fetch_status = 0)
begin
INSERT INTO CHILD
SELECT y.id from Parent y
JOIN x on
y.p_id= #tmpP.p_id
set @pidentity = @@identity
INSERT INTO CHILD @pidentity as child_id,'united kingdom','midlothian'
fetch next from cur into @tmpID
end
close cur
deallocate cur
ta
June 2, 2009 at 3:55 am
vidhyasudha (6/2/2009)
First I create a temp table to hold the data that satisfy the where conditionINSERT INTO #tmpP
select x.p_id, x.col1,x.col2,x.col3 from x where x.colm4 'N'
What is table x?
set @pidentity = 0
DECLARE cur cursdor for
select id from x
open cur
fetch next from cur into @tmpID
What's the point of the cursor? I can't see anywhere where you use @tmpID again
INSERT INTO CHILD
SELECT y.id from Parent y
JOIN x on
y.p_id= #tmpP.p_id
What is table x, and why is the temp table referred to in the where clause when it's no where else in the query?
There should be a simple set-based solution, but I can't figure out what it is that you're doing.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 2, 2009 at 4:36 am
Sorry forgot to mention about table x. Table x is copy of records from other table (old database) that satisfy certain condition. old table contains some test data and irrelvant data that are not necessary for migration.
For example if I have a order table in my old database (which contains order paid by cheque and credit card + test data ) my new database has got table x which contains only creditcard and cheque order. I also got table separately to store credit card and cheque order separately and relating to order payment table.
since i need to extract relevant order I had stored them in temporary table
with query
INSERT INTO #tmpP
select x.p_id, x.col1,x.col2,x.col3 from x where x.colm4 'N' (x is new table contains only relevant data and here im either extracting credit card or cheque order)
now that I have got the order that I need to operate in
I'm performing Join to make sure im not inserting any order other than in table x
with following cursor declaration
declare @pidentity int
set @pidentity = 0
DECLARE cur cursor for
select id from x --gets all order id (CC +Cheque)
open cur
fetch next from cur into @tmpID
while (@@fetch_status = 0)
begin
INSERT INTO Parent (p_id) --parent table is synonym for order_CC or order_cheque
SELECT tmp.p_id from #tmpP as tmp -- temporary table holding either CC or cheque order only
where tmp.id= @tmpID
set @pidentity = @@identity
INSERT INTO CHILD @pidentity as child_id,'united kingdom','midlothian'
--child table is synonym for order payment
fetch next from cur into @tmpID --fetch next orderid
end
close cur
deallocate cur
I hope this might clear many doubts. apology for not giving clear explanation earlier.
June 2, 2009 at 4:39 am
Updatede cursor (posted with clear explanation )
declare @pidentity int
set @pidentity = 0
DECLARE cur cursor for
select id from x --gets all order id (CC +Cheque)
open cur
fetch next from cur into @tmpID
while (@@fetch_status = 0)
begin
INSERT INTO Parent (p_id) --parent table is synonym for order_CC or order_cheque
SELECT tmp.p_id from #tmpP as tmp -- temporary table holding either CC or cheque order only
where tmp.id= @tmpID
set @pidentity = @@identity
INSERT INTO CHILD @pidentity as child_id,'united kingdom','midlothian'
--child table is synonym for order payment
fetch next from cur into @tmpID --fetch next orderid
end
close cur
deallocate cur
June 2, 2009 at 5:25 am
Ok, you've neither given me all the table definitions nor any sample data so I can't test this, but give this a try. It should give you the idea if nothing else
No need for a temp table, no need for a cursor.
DECLARE @OutputTable TABLE (child_id int not null)
INSERT INTO Parent (p_id)
OUTPUT inserted.id INTO @OutputTable
SELECT p_id from x where x.colm4 'N'
INSERT INTO CHILD (child_id, country, region)
SELECT child_id,'united kingdom','midlothian'
FROM @OutputTable outtbl INNER JOIN Parent ON outtbl.child_id = Parent.id
-- to show data
select * from Parent
select * from child
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 2, 2009 at 6:06 am
After your reply , I have googled and read about OUTPUT Clause and it will perfectly improve the performance.
Thank you.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply