Hi Venu,

You need to figure out the unique index that will exclude duplicate rows.
Once you have this information create a duplicate table with a slightly
different name.  If 'select into/bulkcopy' is set as a database option on
your database you can do this by running:

select * into tablenamex from tablename where 1 = 2

This command will create a duplicate table without importing the data.

Otherwise just script the table, change the tablename in the script and run
it.  There are several different ways to do this depending on the version of
SQL you are running.

Next you will want to add a unique index to your new table with the ignore
dup_key 
argument.  

Create unique index indexname on tablenamex (column1, column2,...) with
ignore_dup_key

Now insert your data from your old table to your new table.  Use standard
insert syntax to accomplish this:

insert tablenamex
select * from tablename

Only non-duplicate rows (based on your unique index) will be inserted.
Duplicates will be ignored and a warning will appear on your screen advising
you of this fact.  Now you should have a copy of the table without the
duplicate rows.  You have a couple of options.  You can rename the old table
(sp_rename tablename, old_tablename) and then rename the new table
(sp_rename tablenamex, tablename).  Under this scenario you'll need to
recreate all indexes, foreign keys, triggers, etc that may be missing on the
new table.  Another alternative is to delete the data from the old table
(tablename), and then import the corrected data back from the table that
doesn't contain the duplicates (tablenamex).  I offer this alternative
grudgingly since I'm not familiar with your situation and don't know whether
you need to preserve the duplicate rows.  I would be extremely cautious at
this junction, particularly if you data is valuable.

-----Original Message-----
From: Venugopal A [mailto:arivenu@b...]
Sent: Saturday, November 04, 2000 7:30 PM
To: sql language
Subject: [sql_language] RE: Deleting Duplicate records...


Hi Melissa
  Can u please send me the code for this....

thank you,
bye
venu

Melissa Prush wrote:

> I've written cursors to do this but the easiest method I've found is to
> create a copy of the table with a different name, add a unique natural
index
> to your duplicate table with the ignore dup_key argument (see create index
> in BOL), and then transfer the data into your duplicate table.  The
> duplicate table will only accept a single record for each duplicate.
>
> Melissa
> -----Original Message-----
> From: Venugopal A [mailto:arivenu@b...]
> Sent: Friday, November 03, 2000 5:13 AM
> To: sql language
> Subject: [sql_language] Deleting Duplicate records...
>
> Hi..
>   What's the query to delete a copy of duplicate record...
>  This happens suppose i don't have any primary key in my table...
>
> Thanx in Advance...
>
> bye
> venu
>