Inserting Unique Records

Hello,

I have a table with sixty columns in it, five of which define uniqueness for the records. Currently there are 190,775 records in the table. One of the records is a duplicate. I need to insert only the unique records from this table (all columns) into another table. I cannot use a unique nonclistered index with IGNORE_DUP_KEY in the destination table because of a problem I am having with the 'duplicate key was ignored' message. The destination table has a primary key with a clustered index on the same five columns.

How can I put together a SELECT statement that will give me all of the columns in the source table based on uniqueness of the five key columns?

Does my request make sense? Please let me know if you have questions.

Thank you for your help!

CSDunnThe one way that I know to do this would be to use MAX on all but the key fields in the Select statement of the source table, and group by the key fields.

Is there another way?|||Are you simply trying to ID (in order to eliminate) the one record that is a duplicate?

You might try:

SELECT
col1
, col2
, col3
, col4
, col5
FROM
dbo.MyTable
GROUP BY
col1
, col2
, col3
, col4
, col5
HAVING COUNT(*) > 1

Then copy 1 row with the duplicate data to another table (identically defined with no primary key). Delete the duplicate records from the source table and then re-import the one record from the export table.

Otherwise, yes, you could use MAX for all but the five primary key columns to insert into your other table. Just be sure that it's MAX that you want and not MIN (or some other function).

Regards,

hmscott|||Thanks for your help!

cdun2