PK violation on Insert - why?

[mkrausnick]

I have two identical SQL Server 2005 tables. I want to merge one into the other in the Management console with the command:

insert into MasterTable select * from TransTable

Now previous to this, I check for possible conflicts by executing the command:

select PKField from TransTable where PKField in (select PKField from MasterTable)

This query comes up empty, as does the query when I turn the tables around:

select PKField from MasterTable where PKField in (select PKField from TransTable)

There are no common values in PKField between the two tables. Why do I get a Violation of PRIMARY KEY constraint?


Mike Krausnick
Dublin, California

 

[gmmastros]

I have two identical SQL Server 2005 tables.

Are you sure they are identical. There is a potential problem with the query you posted. If the ordinal position of the columns are not the same, then you will have problems.

Ex:

Table1
Id int
EyeColor VarChar(20)
ShoeSize Decimal(3,1)

Table2
Id Int
ShoeSize Decimal(3,1)
EyeColor VarChar(20)

Since the ordering of the columns are not the same, you will have problems. To correct this, you could try specifying the columns. Ex:

[tt][blue]
Insert Into Table1(Id, EyeColor, ShoeSize)
Select Id, EyeColor, ShoeSize From Table2
[/blue][/tt]

Another possibility... you could have a trigger on the master table that inserts rows in to a different table, and the error message you are seeing is actually from the trigger.

Make sense?


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[mkrausnick]

Thanks for the quick reply. To ensure the tables are identical I deleted TransTable and recreated it using the command:

select * into TransTable from MasterTable where 0=1

and then re-populated TransTable from Visual Foxpro code using SQL pass-through. Same result.

I checked for triggers. There aren't any, other than a set default constraint on one column (not the primary key, obviously).

I also tried:

select * from TransTable Trans inner join MasterTable Mstr on Trans.PKKey=Mstr.PKKey

The query returned no results, as expected. PKKey is denoted as the primary key in the Mgmt Console. It is listed in the index list as 'clustered, unique' whereas all the other indices are listed as 'non-clustered'.

I don't get it. Any other ideas?


Mike Krausnick
Dublin, California

 

[JoeAtWork]

Do you have any other constraint on the primary key field, other than it having to be unique? Perhaps the problem has nothing to do with duplicate ID's.


Joe Schwarz
Custom Software Developer

http://www.plandev.net

 

[SQLBill]

Is the primary key an Identity column?

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[mkrausnick]

There are no constraints on the PK column other than UNIQUE and NOT NULL. In the index tree in the Management Console it shows as being clustered, the only index in the tree that is clustered. I don't know if that's important.

The PK column is a 10-character unique string generated by the host application.

Mike Krausnick
Dublin, California

 

[haroldholt]

Just a thought. What happens when you issue this ?

select PKField, count(PKField) from TransTable
group by PKField
having count(PKField) > 1

 

[haroldholt]

My apologies, I posted the code before the analysis.

[select * into TransTable from MasterTable where 0=1]
will not generate identical tables, e.g. the TransTable will not have the same PK constraint as the MasterTable using this method therefore I thought of the possibility of the TransTable having rows where the 'PKField' column was duplicated.