insertion of node. not terminated due constraints

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

General SQL Server Forums

New to SQL Server Programming

insertion of node. not terminated due constraints

Author

Topic

Nader
Starting Member

41 Posts

Posted - 2010-05-07 : 23:20:59

I do not want the following process to get terminated if one of the rows in the middle of the process has not been able to be inserted due to a unique constraint of email . The unique constrain is required. I want to send the records that might cause a problem to a log table

insert into SP.UserTrial
select * from
(SELECT TempXML.Node.value('(email)[1]','nvarchar(50)') as Email,' ' as Pass,
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,' ' as [Profile],
TempXML.Node.value('(email)[1]','nvarchar(50)') as DisplayEmail,' ' as CellPhone, 1 as UpdatedBy,GETDATE() as UpdateDate, 0 as deleted

FROM @PersonXML.nodes('/enterprise/person') TempXML (Node)) as a

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-05-07 : 23:44:25

So what do you want to do with the record with email existing on the table ? Don't insert it ?


insert into <dest table> ( <column list>)
select *
from   <source table> s
where  not exists 
       (
           select *
           from   <dest table> x
           where  x.email = s.email
       )

KH
[spoiler]Time is always against us[/spoiler]

Nader
Starting Member

41 Posts

Posted - 2010-05-08 : 00:18:37

I tried the following per you suggestion but I got the same error message Violation of UNIQUE KEY constraint 'uq_usertrial_email'.insert into SP.UserTrial
Please let me know what is wrong in what i wrote below

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))as s
where not exists
(select * from SP.UserTrial d where d.Email=s.Email)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-05-08 : 02:33:15

looks like you have duplicate email in the new records that to be inserted. What are you going to deal with these records with same email ?

KH
[spoiler]Time is always against us[/spoiler]

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-05-08 : 03:14:11

You need to loop through each record (either using cursor or while loop) and do exception handling.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-05-08 : 03:28:47

A sample example for your reference.

Set nocount on
Declare @DestTable Table
(Srno int identity,
SCode varchar(10) unique
)

Declare @SourceTable Table
(SCode varchar(10))

Insert into @SourceTable
Select 'FirstVal' Union all
Select 'SecondVal' Union all
Select 'FirstVal' Union all --This will come in exception
Select 'ThirdVal' Union all
Select 'SecondVal' Union all --This will come in exception
Select 'FourthVal'

Declare @Scode varchar(10)

Declare LoopCur Cursor
For Select Scode from @SourceTable

Open LoopCur
Fetch next from LoopCur into @Scode
WHILE @@Fetch_status = 0
Begin
Begin Try
Insert into @DestTable Values (@Scode)
End Try
Begin Catch
Print 'The ''' + @Scode + ''' could not be inserted due to ' + ERROR_MESSAGE()
End Catch
Fetch next from LoopCur into @Scode
End

Select * from @DestTable

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-05-08 : 03:50:16

cursor loop may not be required depending on how OP's requirement on how to handle such duplicates

KH
[spoiler]Time is always against us[/spoiler]

Nader
Starting Member

41 Posts

Posted - 2010-05-08 : 13:44:53

I need to move the records which were not inserted due to duplicate keys or any other reason to a log table.
Performance is very important because my xml files are of size 1G

Thanks again

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-05-09 : 00:51:07

quote:
Originally posted by khtan

cursor loop may not be required depending on how OP's requirement on how to handle such duplicates

KH
[spoiler]Time is always against us[/spoiler]

I agree with you khtan that cursor is not required for the update. But in this scenario, the OP wants to output the error records to other table. The error can be because of unique constraint or any other constraint (The OP has not given the table structure).
In such case I think only looping through the record is solution.

I would be happy to learn it there is an alternate method for this scenario.

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-05-09 : 03:09:27

if the requirement is to only insert one of the duplicate email, record. . .

if you are using SQL 2005 / 2008, you can use row_number() to determine the unique email


select *
from   
(
    select *, row_no = row_number() over (partition by email order by <somecol>)
    from   <source> s
    where  not exists
          (
               select *
               from   <target> t
               where  t.email = s.email
          ) 
) t
where t.row_no = 1

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources