Please Help with Unique Key Constraint error

[YL5956]

Hello all,
Looking at the query below I should not be inserting any rows into table1 where the an_seq field already exist.

The results are consist with the select query in terms of I only retrieve rows from table2 that current do not exist on table1 but when I try to insert the new rows into table1 the error appears. Thank.


Server: Msg 2627, Level 14, State 2, Line 1
Violation of UNIQUE KEY constraint 'IX_arc_tblxCSA_AgencyName_1'. Cannot insert duplicate key in object 'arc_tblxCSA_AgencyName'.
The statement has been terminated.

Insert into arc_tblxCSA_AgencyName
(an_Seq
,an_OrderingName
,an_AgencyName
,an_xorgSeq
,an_Inactive_DtTm
,an_LastUpdated_DtTm
,an_LastUpdated_By
)
Select an_Seq
,an_OrderingName
,an_AgencyName
,an_xorgSeq
,an_Inactive_DtTm
,an_LastUpdated_DtTm
,an_LastUpdated_By
From dbo.tblxCSA_AgencyName an
Where an.an_Seq NOT IN (Select a.an_Seq From arc_tblxCSA_AgencyName a)

 

[tlbroadbent]

Where Not In can return erroneous results under certain conditions. Try using Not Exists.

Insert into arc_tblxCSA_AgencyName
(an_Seq
,an_OrderingName
,an_AgencyName
,an_xorgSeq
,an_Inactive_DtTm
,an_LastUpdated_DtTm
,an_LastUpdated_By
)
Select an_Seq
,an_OrderingName
,an_AgencyName
,an_xorgSeq
,an_Inactive_DtTm
,an_LastUpdated_DtTm
,an_LastUpdated_By
From dbo.tblxCSA_AgencyName an
Where NOT Exists
(Select * From arc_tblxCSA_AgencyName Where an_seq=an.an_Seq)
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[YL5956]

I tried the Not exists statement and I'm still receiving the same error.

Server: Msg 2627, Level 14, State 2, Line 1
Violation of UNIQUE KEY constraint 'IX_arc_tblxCSA_AgencyName_1'. Cannot insert duplicate key in object 'arc_tblxCSA_AgencyName'.
The statement has been terminated.

Insert into arc_tblxCSA_AgencyName
(an_Seq
,an_OrderingName
,an_AgencyName
,an_xorgSeq
,an_Inactive_DtTm
,an_LastUpdated_DtTm
,an_LastUpdated_By
)
Select an_Seq
,an_OrderingName
,an_AgencyName
,an_xorgSeq
,an_Inactive_DtTm
,an_LastUpdated_DtTm
,an_LastUpdated_By
From dbo.tblxCSA_AgencyName an
Where NOT Exists
(Select * From arc_tblxCSA_AgencyName Where an_seq=an.an_Seq)

 

[cbeggan]

This might be a bit of a 'duhh' suggestion, and apologies if so, but have you done an sp_help on the table and checked out the constraints - there may be contraints on other fields. With a name like 'IX_arc_tblxCSA_AgencyName_1' it sounds like there may be more than one constraint on the table....

------
Dublin, Ireland.

 

[tlbroadbent]

I agree. It appears that the agencyname column is causing the error. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[SQLBill]

Here's what MIGHT be happening...and I'm assuming that one of the columns you are trying to insert is a UNIQUE key field.

TABLE 1 TABLE 2
UNIQ_KEY Data UNIQ_KEY Data
1 ALPHA 1 ALPHA
2 BETA 2 BETA
3 DELTA 3 CHARLIE

CHARLIE exists in table 2 but not in table 1. If you try to insert the whole row into table 1 it will fail because it violates the UNIQUE KEY constraint. Solution might be to not select the column that is the unique key or update the unique key before inserting it.

To check if this is the issue, find out which column is the UNIQUE KEY and SELECT that column from both tables (two different selects) and compare them. Are they the same?

-SQLBill __________________________________
'MS SQL Server' NOT LIKE 'MS Access SQL' and
'MS SQL Server' NOT LIKE 'ORACLE SQL' and
'MS SQL Server' NOT LIKE 'Anyother SQL'