log records not inserted due to duplicate key erro

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

log records not inserted due to duplicate key erro

Author

Topic

Nader
Starting Member

41 Posts

Posted - 2010-05-09 : 23:17:24

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 not 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 so important because the xml files are huge they are of 1 gb size.
below is my code two records has the same email intentionaly 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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-10 : 14:21:00

just add like


insert into SP.UserTrial (Email,FirstName,LastName,DisplayName,DisplayEmail)
SELECT *
FROM
(
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) 
)t
WHERE NOT EXISTS (SELECT 1 FROM SP.UserTrial WHERE DisplayEmail = t.DisplayEmail)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources