Bulk insert

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I need to insert nearly more than 6000 records in a single shot.
For that I need to get the data from tree view (windows app) control,
Im reading through recursive mannner to get parent & childs and im forming
an xml file then sending to backend.

here to compose the xml file from tree view control it is taking 25 secs
time and
at back end I returned and Stored procedure for bulk insert.

Create PROC insertversion
(
@VersionData ntext
,@Update bit
)
AS



DECLARE @hDoc int
EXEC sp_xml_preparedocument @hDoc OUTPUT,@VersionData

IF @Update = 1
BEGIN
INSERT INTO Version_Map (version_id, question_id, parent_question_id,
disp_sequence_nbr, created_by, created_date, updated_by, updated_date)
SELECT * FROM OPENXML(@hDoc, 'ROOT/QUESTIONS/QUESTION',1)
WITH ( VERSION_ID int , QUESTION_ID INT, PARENT_QUESTION_ID int, SEQNO
int, created_by varchar(20), created_date datetime, updated_by varchar(20),
updated_date datetime) XMLVersion
END
ELSE
BEGIN
INSERT INTO Version_Map (version_id, question_id, parent_question_id,
disp_sequence_nbr, created_by, created_date)
SELECT * FROM OPENXML(@hDoc, 'ROOT/QUESTIONS/QUESTION',1)
WITH ( VERSION_ID int , QUESTION_ID INT, PARENT_QUESTION_ID int, SEQNO
int, created_by varchar(20), created_date datetime) XMLVersion

END


EXEC sp_xml_removedocument @hDoc



GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Here Im using the OpenXml method... for bulk insert this is taking 50 secs..

Total insert opration it is taking 84 secs to do this operation...

but Im looking a solution to this operation with in 10 secs..

plz help me ....
thanks ....
 
I would check out the Bulk Copy functionality in TSQL. I expect you'll be
able to eliminate the need to transform into XML if you do.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
I agree w/ Bill.
that bcp function as well as the BULK INSERT does some amazing things.
i have a db table with approx 1.6million rows and bcp takes care of the whole table in less than 1.5 minutes. another table has 27,159 rows and thats 1/3 of a second to execute.
thanks
rik



**********************************************************************
Sent via Fuzzy Software @ http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
 
Back
Top