Mega Code Archive
Altering the Table to Add an Identity Column
<![CDATA[
7> CREATE TABLE Dupes(
8> ID int NOT NULL,
9> Txt char (10) NOT NULL
10> )
11> 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> ALTER TABLE Dupes
4> ADD
5> Ident int NOT NULL IDENTITY (1, 1)
6> GO
1>
2>
3> --Deleting Duplicates with a Correlated Subquery
4> DELETE D1
5> FROM
6> Dupes AS D1
7> WHERE
8> D1.Ident >
9> (
10> SELECT
11> MIN (D2.Ident)
12> FROM
13> Dupes AS D2
14> WHERE
15> D2.ID = D1.ID
16> AND
17> D2.Txt = D1.Txt
18> )
19>
20>
21> drop table dupes;
22> GO
(3 rows affected)
1>
]]>