DTS omit duplicate rows. (1 Viewer)

rsbutterfly16

Registered User.
Local time
Today, 07:57
Joined
Jun 5, 2006
Messages
77
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.
 

KeithG

AWF VIP
Local time
Today, 07:57
Joined
Mar 23, 2006
Messages
2,592
Put Distinct after Select in your SQL statement
 

rsbutterfly16

Registered User.
Local time
Today, 07:57
Joined
Jun 5, 2006
Messages
77
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:
 

KeithG

AWF VIP
Local time
Today, 07:57
Joined
Mar 23, 2006
Messages
2,592
It sounds like you have a duplicate value in your Primary key not a duplicate record.
 

rsbutterfly16

Registered User.
Local time
Today, 07:57
Joined
Jun 5, 2006
Messages
77
is there any way to skip the ones that have duplicate values int the PK?
 

rsbutterfly16

Registered User.
Local time
Today, 07:57
Joined
Jun 5, 2006
Messages
77
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.
 

Users who are viewing this thread

Top Bottom