Hi guys , I am trying to do a dts in which copies a table with duplicates, and then puts the data but it cannot have any duplicates. What is the best way to do this? I remember long time ago I saw a trick of how to do this by coping the table and then inserting a pk into the table, not sure of how it was, but I don’t seem to find the article … I don’t want to omit all the duplicates, I have to keep one row and then omit the other copies.

Put Distinct after Select in your SQL statement

thank you Keith, but that is the first thing i tried, in the source tab in DTS wizard where i have the select statememt i put Select Distinct .....

but i stilll keep gettting the error the task reported failure on execution the statement has been terminated cannot insert duplicate key row ..... :confused:

It sounds like you have a duplicate value in your Primary key not a duplicate record.

is there any way to skip the ones that have duplicate values int the PK?

hi Keith so this is what i did and it seems to have worked, i schedule dto do this once every month.

this DTS is very basic just copies a table(it has duplicates) from another database and then put the data into my table with different column names.
In my table i inserted a pk autonumber identity column and check on the ignore duplicate key. then run the dts again, it does error out saying that ignore duplicate keys were ignored. and all the rows got inserted into my table :-).

Did i do this correctly? i tested and all the rows are there, just skipped the ones that had duplicate records only inserted one of each which is what i wanted.