Script for deleting duplicates in mulitple tables
-
Anyone have a good script for viewing and then ultimately deleting duplicate records in multiple tables?
-
-
There are several ways to eliminate dupes.
Is this a one time thing? Or do you continually have to scrub do to an import?
-JG
-JG -
Looks like it will be a regular scrub due to imports.
-
Well there are lots of methods to deal with this, however to save you a lot of troubles you may want to setup an import table to do your scrubbing then move to the main table. But the basics of finding duplicate goes soemthing like this
SELECT COLUMNS FROM tblX HAVING COUNT(DUPICATETOCHECKFOR) > 1 GROUP BY COLUMNS
Just dig around in the scripts here but to give you an idea of what best woirks for your needs a DDL of the table and sample of type of data would be helpfull.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
-
I recommend using a seperate table for the import. Create an index made of all the columns on the table with "Ignore Dupes" checked then populate the rest of your tables with your new table.
-JG
-JG -
Agree with jgee. I import into a table that duplicates the setup on the main table.
Then I update the main table from the import using the PK as a match field.
Next I delete from the import table, joining to the main table on the PK. This removes all matching rows.
Everything left is an insert into the main table.
Steve Jones
-
Great! Thanks, I will give that a try.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply