Deleting duplicate records

[Dinesh.T.K]

Jon,

You didnt supply the DDL, so I can only point to existing practices to
remove duplicate records.Here they are:

INF: How to Remove Duplicate Rows From a Table
[url]http://support.microsoft.com/default.aspx?scid=kb;EN-US;q139444[/url]

INF: Removing Duplicate Rows from an Existing Table in SQL
[url]http://support.microsoft.com/default.aspx?scid=kb;EN-US;q70956[/url]

Removing Duplicate Records
[url]http://www.databasejournal.com/features/mssql/article.php/1438651[/url]

>>>> Should I use primary keys to prevent this?

Yes or a UNIQUE constraint depending upon the business logic.

>>>Would the job fail or just not load the dups?

May be you can load it first to a temp table and then use the logic
mentioned in the above articles before inserting to the main table.


--
Dinesh.
SQL Server FAQ at
[url]http://www.tkdinesh.com[/url]

"Jon" <jonscott@smartneighborood.net> wrote in message
news:055701c34660$d76633f0$a301280a@phx.gbl...

>
> Hi,
>
> I have duplicate records in a table that need to be
> removed. Any coding help to remove these records would be
> appreciated. Also ideas on preventing would be helpful.
> Should I use primary keys to prevent this? Sorry new to
> SQL coding. Also if setting keys would prevent this what
> would happen when I BCP data into the table and the file
> contained dups? Would the job fail or just not load the dups?
>
> Thanks,
>
> Jon

[Art Russell]

The help file is a bit thin in its description of how to locate and delete
"excess" duplicate records.

Locating duplicate records is straight forward (!), but how does one go
about finding duplicate records in excess of the first instance of a record?
Once found, I assume a simple delete all records is appropriate, assuming
that only duplicate records are displayed.

Any help or pointers would be appreciated.

Thanks - Art

[Bridget Eley]

Actually, the online help is very helpful in this respect. Read the article
"Finding duplicate values using a self-join relationship" (a search on
"duplicate records" will find it). It will explain precisely what you need
to do to achieve your goal.

Bridget Eley

in article BB5EF188.52A21%artrussell@mindspring.com, Art Russell at
[email]artrussell@mindspring.com[/email] wrote on 13/8/03 9:33 AM:

> The help file is a bit thin in its description of how to locate and delete
> "excess" duplicate records.
>
> Locating duplicate records is straight forward (!), but how does one go
> about finding duplicate records in excess of the first instance of a record?
> Once found, I assume a simple delete all records is appropriate, assuming
> that only duplicate records are displayed.
>
> Any help or pointers would be appreciated.
>
> Thanks - Art
>

[Michael Myett]

Art Russell wrote:

> The help file is a bit thin in its description of how to locate and delete
> "excess" duplicate records.
>
> Locating duplicate records is straight forward (!), but how does one go
> about finding duplicate records in excess of the first instance of a record?
> Once found, I assume a simple delete all records is appropriate, assuming
> that only duplicate records are displayed.
>
> Any help or pointers would be appreciated.
>
> Thanks - Art
>

In FileMaker ver 5 and above from the Users Guide, chapter 10, section
13, titled Example of a Scriptmaker Script. In ver 4 it is chapter 5,
section 26. This walks you through setting up and scripting a method of
finding all duplicates. Also if you search for "finding duplicates" in
the knowledgebase at filemaker.com you will find two more techniques.
This should help. Best of luck.

Michael Myett