Remove field from composite key and collate duplicate data

Question

I'm looking for the best way to go about changing the primary key on a table in my DB without having to completely reinsert data from files again. The modification I'm going to make is going to remove part of the primary key, which will result in tens of thousands of rows which need to be collated.

The part of the key being removed is a date field, which originally was needed to help uniquely identify incoming data. However, the way this field is assigned at the source (outwith my control) has since changed and we now want to only keep the earliest occurrence of a particular record and discard the later occurrences.

The rest of the fields in the key (4 FK's) will produce unique records.

I'm assuming I'll need to transfer the data to another table, make the change and then re-insert? Or is there a better way?

Answer 1

If the four records dictate a unique and you want the first one by date, then why not insert into another table, truncate, then insert back once you finish the table reconfigure (or create a new table with the right columns, insert into, then rename after dropping?) I don't think this can be improved upon, unless you want to delete from where not equal to, but the insert into should be faster (needs to be tested to prove for sure)

Answer 2

--Drop existing Primary Key
ALTER TABLE [<table>] DROP CONSTRAINT [<pk_constraint>];
GO

--Delete all rows except the first for each <Id>, ordered by <MyDateCol>
WITH delCTE AS (
  SELECT <Id>, ROW_NUMBER() OVER(PARTITION BY Id ORDER BY <MyDateCol>) AS RowNum
  FROM dbo.<Table>
)
DELETE a
FROM <table> a
JOIN delCTE b
  ON b.<Id> = a.<Id>
WHERE b.RowNum > 1;
GO

--Create the new Primary Key
ALTER TABLE [<table>] ADD CONSTRAINT [<pk_constraint>] PRIMARY KEY (<cols>);
GO

Answer 3

You can copy your table's data to a file using BCP (bulk copy), truncate the table, remove the old PK, create new PK (only 4 columns), import data from bcp file using a script (like Eric's script) that ignores the duplicates.

For many rows nothing can beat bulk copy :).