如何在重复键错误后不中止进程? [英] How to not abort a process after a duplicate key error ?

问题描述

我正在将数据从xml文件导入到已经存在的表中.该表具有唯一的关键电子邮件，以免添加重复的记录.如果其中一条记录具有表中已经存在的电子邮件，则导入过程将中止.如何让过程继续进行并将由于重复键错误而未插入的记录移动到日志文件?
性能和时间很重要，因为xml文件很大，它们的大小为1 gb.
下面是我的代码:两个记录有一封相同的电子邮件，目的是测试重复的密钥问题.没有记录由于重复键而被插入.
感谢您的帮助.

I am importing data from xml file to an already existing table. The table has a unique key email so as not to add duplicate records. The process of importing gets aborted if one of the records has an email which already exists in the table. How to let the process continue and move the records that were not inserted due to duplicate key error to a log file?
Performance and time is important because the xml files are huge they are of 1 gb size.
Below is my code: Two records has the same email intentionally to test the duplicate key problem. none of the records get inserted due to duplicate key.
Appreciate your help.

set @PersonXML=''<?xml version="1.0" encoding="UTF-8"?>
<enterprise>
<person>
<name>
<fn>Lee Terence</fn>
<n>
<family>Lee</family>
<given>Terence</given>
</n>
</name>
<email>[e-mail]</email>
</person>
<person>
<name>
<fn>John Jack</fn>
<n>
<family>John</family>
<given>Jack</given>
</n>
</name>
<email>[e-mail]</email>
</person>
<person>
<name>
<fn>John Jack</fn>
<n>
<family>John</family>
<given>Jack</given>
</n>
</name>
<email>[e-mail]</email>
</person>
</enterprise>
''
insert into SP.UserTrial (Email,FirstName,LastName,DisplayName,DisplayEmail)
SELECT TempXML.Node.value(''(email)[1]'',''nvarchar(50)'') as Email,
			  TempXML.Node.value(''(name/n/given)[1]'', ''nVARCHAR(50)'') as FirstName,
			 TempXML.Node.value(''(name/n/family)[1]'', ''VARCHAR(50)'') as LastName,
			  TempXML.Node.value(''(name/fn)[1]'',''nvarchar(50)'') as DisplayName,
			  TempXML.Node.value(''(email)[1]'',''nvarchar(50)'') as DisplayEmail
			  FROM @PersonXML.nodes(''/enterprise/person'') TempXML (Node)

没有插入任何记录，因为其中两个具有相同的电子邮件
sp.usertrial将电子邮件作为唯一密钥

违反UNIQUE KEY约束"uq_usertrial_email".无法在对象"SP.UserTrial"中插入重复的密钥.

------------------
如果没有人重复发送电子邮件，则可以正常工作，并且可以将记录插入.

谢谢
_

None of the records get inserted because two of them has the same email
sp.usertrial has email as a unique key

Violation of UNIQUE KEY constraint ''uq_usertrial_email''. Cannot insert duplicate key in object ''SP.UserTrial''.

------------------
If none has a duplicate email it works fine and the records are inserted fine.

Thanks
_

推荐答案

请在t-sql语句中使用try catch块，并使用@@ ERROR(系统函数).

[

please use the try catch block in t-sql statement and use @@ERROR (system function ).

[http://msdn.microsoft.com/en-us/library/ms175976.aspx]

Please let us know if you get things going or still face same problem.

这篇关于如何在重复键错误后不中止进程?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！