Strange primary key behaviour

[Freehansje]

Consider the following:
There is a table with 2 attributes, COPInstantieID as Int and LoketID as Int. Those 2 attributes are also the primary key. At some point an ExecuteSQL task runs this script:

INSERT INTO Centraal.InstantieLoket(
COPInstantieID,
LoketID
)
SELECT t1.COPInstantieID,
1
FROM Centraal.Instantie t1
LEFT OUTER JOIN Centraal.InstantieLoket t2
ON t1.COPInstantieId = t2.COPInstantieId
WHERE t2.COPInstantieId IS NULL

It INSERTS duplicate keys... When I run this scripts in Management Studio it does NOT insert duplicate keys.

Some more info on the whole proces:
This packages checks an importfile for new 'Instanties'. These 'Instanties' may or may not already exists in the targettable 'Centraal.Instantie'. If they do not exist yet a record is inserted into 'Centraal.InstantieLoket'. What I do is run the package with 2 NEW 'Instanties', which are processed as expected; 2 new records are added into 'Centraal.InstantieLoket'. Next I run the package again with the same records in the importfile. These records are processed again, they do exist so the script above should return no NULLS, as it does in Management Studio. Yet 2 records are inserted into 'Centraal.InstantieLoket', which is not possible because of primary key constraint.
When I run the package a 3rd time no new records are inserted into 'Centraal.InstantieLoket'.

These duplicate records can not be deleted from the table directly; I have to write a DELETE statement to accomplish this.
Any idea how this behaviour can be explained?

 

[mrdenny]

A corrupt primary key index could cause this. Try running a DBCC CHECKDB on the database, or rebuild the index manually.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[Freehansje]

Right, this seems to be working, tnx.

 

[mrdenny]

No problem.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog