在存储过程中使用xml插入 [英] Insert using xml in stored procedure
本文介绍了在存储过程中使用xml插入的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
这是我的存储过程.我正在使用xml插入批量数据20000行
我想快速处理
我正在从excel读取数据，并使用xml将其发送到存储过程.
这可以正常工作.
但是要花很长时间我要快速处理
请检查我的存储过程
This is my stored procedure.I am inserting bulk data 20000 rows using xml
And i want fast processing
I am reading data from excel and sending it to stored procedure using xml.
This works properly.
But takes long time i want fast processing
please check my stored procedure
@XML_TRANSACTIONDETAILS nText,
@status int,
@username int,
@urlorcatalog nvarchar(100),
@dataentrytype nvarchar(100)
AS
DECLARE @docHandle int
SET NOCOUNT ON;
EXEC sp_xml_preparedocument @docHandle OUTPUT, @XML_TRANSACTIONDETAILS
-- CREATE TEMP TABLE
CREATE TABLE #TempTable
(
maxid bigint NULL,
companyname nvarchar(100) NULL,
email nvarchar(100) NULL,
website nvarchar(100) NULL,
country nvarchar(100) NULL,
contactperson nvarchar(100) NULL,
telphone nvarchar(100) NULL,
mobile nvarchar(100) NULL,
fax nvarchar(100) NULL,
region nvarchar(100) NULL,
status int NULL,
username int NULL,
date datetime NULL,
category INT NULL,
urlorcatalog nvarchar(100) NULL,
dataentrytype nvarchar(100) NULL
)
// here i am inserting data from xml into temp table
-- INSERT INTO TEMP TABLE DETAILS OF EMAIL
INSERT INTO #TempTable
( maxid,companyname,email,website,country,contactperson,telphone,mobile,fax,region,status,username,date,category,urlorcatalog,dataentrytype )
SELECT data.maxid,data.companyname,data.email,data.website,data.country,data.contactperson,data.telphone,data.mobile,data.fax,REGION.ID,@status,@username,GETDATE(),CATEGORY.ID,@urlorcatalog,@dataentrytype
FROM OPENXML(@docHandle,N'master/TRANSACTION',3)
WITH
(
companyname varchar(100),
website varchar(100),
country varchar(100),
contactperson varchar(100),
telphone varchar(100),
mobile varchar(100),
fax varchar(100),
region varchar(100),
status int,
username int,
--date datetime,
category varchar(100),
urlorcatalog varchar(100),
email varchar(100),
email1 varchar(100) ,
email2 varchar(100),
maxid bigint,
dataentrytype varchar(100)
) as data
LEFT OUTER JOIN category CATEGORY ON data.category=CATEGORY.categoryname
LEFT OUTER JOIN regioninfo REGION ON data.region=REGION.regionname
//here i am updating temp table if category and region null
--**********************UPDATE TEMP TABLE IF CATEGORY AND REGION IS NULL********************
UPDATE #TempTable
SET
category=5
WHERE category is NULL
UPDATE #TempTable
SET
region=2
WHERE region is NULL
// this code for counting duplicates entry.
-- ******************* CODE TO COUNT DUPLICATES EMAILS ***************************
SELECT COUNT(*) FROM
(
SELECT tmp.*
FROM #TempTable tmp
LEFT OUTER JOIN emailinfo Email ON tmp.email=Email.email
)AS T
WHERE --T.RowNumber1>1 AND
T.email !=''
AND
EXISTS (SELECT * FROM emailinfo WHERE email=T.email)
-- TRANSACTION BEGINS HERE
BEGIN TRANSACTION
// here updating only status field email already existed in condition
-- UPDATE STATUS IF EMAIL ALREADY EXISTED
UPDATE companyinfo
SET companyinfo.status=RESULT.status
FROM
(
select TEMP1.status,TEMP1.DataID
from (
SELECT tmp.email,tmp.status,Email.DataID,ROW_NUMBER() OVER (PARTITION BY tmp.email ORDER BY tmp.email ) AS 'RowNumber'
FROM #TempTable tmp
LEFT OUTER JOIN emailinfo Email ON tmp.email=Email.email
WHERE
tmp.email !=''
AND
EXISTS (SELECT * FROM emailinfo WHERE email=tmp.email)
)AS TEMP1
LEFT OUTER JOIN companyinfo COMPANY ON TEMP1.DataID=COMPANY.ID
where --cmp.status !=8 And
TEMP1.RowNumber=1 AND
COMPANY.status !=1 AND
COMPANY.status < 6 AND
TEMP1.status < COMPANY.status
) AS RESULT
WHERE companyinfo.ID=RESULT.DataID
IF @@ERROR<>0 BEGIN ROLLBACK TRANSACTION RETURN -101 END
-- ************INSERT INTO PRIMARY TABLE(companyinfo)*************
INSERT INTO companyinfo
-- (
-- ID,companyname,website,country,contactperson,telphone,mobile,fax,region,status,username,date,category,urlorcatalog
-- )
SELECT
T1.maxid,T1.companyname,T1.website,T1.country,T1.contactperson,T1.telphone,T1.mobile,T1.fax,T1.region,T1.status,T1.username,T1.date,T1.category,T1.urlorcatalog,T1.dataentrytype,null
FROM
(
SELECT tmpdata.*, ROW_NUMBER() OVER (PARTITION BY maxid ORDER BY maxid ) AS 'RowNumber'
FROM
(
SELECT
*
FROM
(
SELECT tmp.*, ROW_NUMBER() OVER (PARTITION BY tmp.email ORDER BY tmp.email ) AS 'RowNumber1'
FROM #TempTable tmp
LEFT OUTER JOIN emailinfo Email ON tmp.email=Email.email
-- LEFT OUTER JOIN category CATEGORY ON tmp.category=CATEGORY.ID
-- LEFT OUTER JOIN regioninfo REGION ON tmp.region=REGION.ID
)AS T
WHERE T.RowNumber1=1
AND T.email !=''
AND
NOT EXISTS (SELECT * FROM emailinfo WHERE email=T.email)
)tmpdata
)AS T1
WHERE
T1.email !='' AND
T1.RowNumber=1
-- ************INSERT INTO SECONDARY TABLE*************
INSERT INTO emailinfo
(
DataID,email
)
SELECT
T.maxid,T.email
FROM
(
SELECT tmp.*, ROW_NUMBER() OVER (PARTITION BY tmp.email ORDER BY tmp.email ) AS 'RowNumber'
FROM #TempTable tmp
LEFT OUTER JOIN emailinfo Email ON tmp.email=Email.email
)AS T
WHERE T.RowNumber=1
AND T.email !=''
AND
NOT EXISTS (SELECT * FROM emailinfo WHERE email=T.email)
IF @@ERROR<>0 BEGIN ROLLBACK TRANSACTION RETURN -101 END
COMMIT TRANSACTION
EXEC sp_xml_removedocument @docHandle
如果有任何优化，请给我建议
谢谢...............
please give me suggestion for any optimization if any
Thanks ...............
推荐答案
您进行了很大的插入，这需要时间.
最快的方法是BulkCopy功能:请参见 http://stackoverflow.com/questions/5171430 /import-excel-file-to-sql-using-bulkcopy [ ^ ]
you do a very big insert, that takes time.
fastest way is BulkCopy function: see http://stackoverflow.com/questions/5171430/import-excel-file-to-sql-using-bulkcopy[^]
这篇关于在存储过程中使用xml插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
