4> CREATE TABLE Dupes(
5> ID int NOT NULL,
6> Txt char (10) NOT NULL
7> )
8> GO
1> INSERT Dupes (ID, Txt) VALUES (1, 'x')
2> INSERT Dupes (ID, Txt) VALUES (1, 'a')
3> INSERT Dupes (ID, Txt) VALUES (1, 'x')
4> INSERT Dupes (ID, Txt) VALUES (1, 'x')
5> INSERT Dupes (ID, Txt) VALUES (2, 'b')
6> INSERT Dupes (ID, Txt) VALUES (2, 'x')
7> INSERT Dupes (ID, Txt) VALUES (2, 'b')
8> INSERT Dupes (ID, Txt) VALUES (3, 'c')
9> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2>
3> --Creating a Temporary Table of Distinct Rows
4>
5> SELECT ID, Txt INTO #Singles
6> FROM
7> Dupes
8> GROUP BY
9> ID,
10> Txt
11> HAVING
12> COUNT (*) > 1;
13> GO
(2 rows affected)
1>
2> --Removing the Duplicates
3> DELETE d FROM Dupes AS D JOIN
4> #Singles AS S ON S.ID = D.ID
5> AND S.Txt = D.Txt;
6> GO
(5 rows affected)
1>
2> --Inserting the Former Duplicates
3>
4> INSERT Dupes
5> SELECT
6> *
7> FROM
8> #Singles
9>
10> drop table dupes;
11> GO
(2 rows affected)
1>