Click here to Skip to main content
15,903,362 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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>tlee11@xyz.com</email>
</person>
<person>
<name>
<fn>John Jack</fn>
<n>
<family>John</family>
<given>Jack</given>
</n>
</name>
<email>JohnJack@xyz.com</email>
</person>
<person>
<name>
<fn>John Jack</fn>
<n>
<family>John</family>
<given>Jack</given>
</n>
</name>
<email>JohnJack@xyz.com</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)

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
_
Posted
Updated 9-May-10 19:32pm
v2

1 solution

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.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900