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 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 ....