Duplicate records - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Duplicate records

Author

Topic

ballabhoks
Starting Member

18 Posts

Posted - 2003-12-11 : 00:36:03

Dear All,
I am facing a problem in finding duplicate recrods from a table. I want to find duplicate records on the basis of certain fields. Apart from this I have a primary key in my table and I have created a Index on those fields on the basis i want duplicate records.

Thanks in advance.
Ballabh

JCamburn
Starting Member

31 Posts

Posted - 2003-12-11 : 01:10:57

Join the table to itself. For example, given the following table:

CREATE TABLE dbo.Table1
(
  ID integer NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
  ColumnA varchar(50) NOT NULL,
  ColumnB varchar(50) NOT NULL
)  ON [PRIMARY]
GO

Your query would be:

SELECT
  ID = a.ID,
  DuplicateID = b.ID
FROM
  Table1 AS a
  INNER JOIN
  Table1 AS b
  ON a.ColumnA = b.ColumnA And a.ColumnB = b.ColumnB

to find duplicates for the values of ColumnA and ColumnB. If you don't want the same matches repeated multiple times, then you have to make other changes to the query. You can find out more by searching this site for "finding duplicates".

nr
SQLTeam MVY

12543 Posts

Posted - 2003-12-11 : 05:00:00

If you only want duplicate then you need "and a.ID <> b.ID" in the where clause.
Easier is
select col1, col2
from tbl
group by col1, col2
having count(*) > 1

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources