Strange SQL Error

I setup a DTS package in SQL 2000 yesterday to make a mirror of the live database for an app that I maintain. The goal was to have an exact copy of the live data for the dev environment so I can start coding a new release. I set the DTS package to run every 4 hours. Little did I know the columns specified as primary keys didn’t retain that property. When I went in to look at the data there was all sorts of duplicates. Attempting to delete these records threw the following error:

The row value(s) updated or deleted either do not make the row unique or they alter multiple rows.

I ran across this site that had a fix for my boneheadedness (now I’m making up words). I used the 3rd solution where you add a new field and set it as a key which allows you to delete the duplicate data, remove the new column, and re-set your primary key as the primary key.

Solution 3

This solution is a yet another way to approach this problem.  In this approach you use the query below to create a new column that numbers your records 1, 2, 3, etc.  This will get rid of the duplication and allow you to delete the records or update them as you need.  After you fix the duplication problem, just remember to delete the column.

 

ALTER TABLE Table1

ADD TempID int IDENTITY(1, 1)

Thanks to Allen Buckley for that one. I was scratching my head.