Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 was 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?

share|improve this question

3 Answers 3

up vote 4 down vote accepted

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)

share|improve this answer
    
Had a go at the various options provided but creating a separate table seems to be the most suitable for my case in terms of performance. Cheers –  GShenanigan Jan 24 '11 at 10:59
--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
share|improve this answer
    
without profiling I'm unconvinced that removing the indexes will speed things up, altho I understand why you suggest it will –  jcolebrand Jan 19 '11 at 19:03
    
You could drop the PK constraint after the delete. Your performance may vary. –  Eric Humphrey - lotsahelp Jan 19 '11 at 19:26

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 :).

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.