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
_