Hello all.

I'm desperate here.

I Have a table with 5 columns.

CREATE TABLE MyTable
(M_ID smallint,
A_ID smallint,
AW char(10),
ST_O char(10),
MC char(300) )

I didn't set any primary key at the beginning, so i could import data from a txt file through the SQL import and Export Wizard(3 million records). This txt contains many duplicate records (rows are same in all columns) but it contains also duplicate records in M_ID and A_ID fields. I want to make these fields PK.

I already cleaned up the table from the rows that are exactly the same (with only one instance left - i used the method with the temp table), but i cannot delete the rows wich contains the duplicate values in the two first columns, and leave only one of them. I don't care which of the rows, i just want the duplicates to be deleted, so there is only one compination M_ID and A_ID.

for instance...

 M_ID A_ID AW ST_O MC
====== ====== ====== ====== ======

123 981 FIR REL MI
123 981 SEC RELTG PS
256 315 ALO TRM PP
256 315 POW DTM KL
473 787 LKJ ERT IO
473 787 JKM WSA IK

i want to have only

 M_ID A_ID AW ST_O MC
====== ====== ====== ====== ======

123 981 FIR REL MI
256 315 ALO TRM PP
473 787 LKJ ERT IO

Hope i made my self clear. :)

All this is for a project for my university.(i must have it ready till tomorrow)

Thanks in advance!

Here is a script to review. Let me know if this works for you...

-- Add IDENTITY column
ALTER TABLE dbo.MyTable ADD MyTableID int IDENTITY(1,1) NOT NULL;

-- Remove Duplicates
DELETE a
FROM dbo.MyTable a
JOIN(SELECT MIN(MyTableID) maxid, M_ID, A_ID FROM dbo.MyTable GROUP BY M_ID, A_ID) b ON a.M_ID = b.M_ID AND a.A_ID = b.A_ID
WHERE a.MyTableID != b.maxid;

-- Drop IDENTITY Column
ALTER TABLE dbo.MyTable DROP COLUMN MyTableID;

-- Add CONSTRAINT
ALTER TABLE ADD CONSTRAINT PK_MyTable PRIMARY KEY NONCLUSTERED (M_ID, A_ID);

Adam Gossage
Lake Wylie, SC, USA

worked perfectly!

i don't have words to thank you!

so thank you! :)

only issue with this solution is if your doing the alter table while the table is in heavy use transactions will likely be rejected by the server. I like using cursors but it's not needed if your not in a high volume, in production situation with that table.