Below you will find two methods that can be used to remove duplicate records from you Microsoft SQL Server tables.
Method 1:
This method will only work in SQL Server 2005 and later; if you need to remove duplicates from an earlier version please upgrade you SQL Server version. Just kidding! Use the technique in method 2. Actually you really should upgrade your version of SQL Server…
This method uses the Common Table Expression functionality and row_number function which was introduced in Microsoft SQL Server 2005. The key strategy to this method is to generate a number next to the records which denotes the row number.
So in essence we want to transform this:
Duplicated Records
To this:
Duplicated Results with Numbers
And remove all instances of those rows which the row number column do not correspond to 1.
Below is a script which can be used to demonstrate this. Also you can use this link in SQL Fiddle.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
-- Create Table CREATE TABLE PhoneticAlphabet (Alphabet char(1), CodeWord varchar(20)) -- Load Sample Data in Table INSERT INTO PhoneticAlphabet VALUES ('A', 'Alfa') INSERT INTO PhoneticAlphabet VALUES ('B', 'Brave') INSERT INTO PhoneticAlphabet VALUES ('C', 'Charlie') INSERT INTO PhoneticAlphabet VALUES ('D', 'Delta') INSERT INTO PhoneticAlphabet VALUES ('E', 'Echo') INSERT INTO PhoneticAlphabet VALUES ('F', 'Foxtrot') INSERT INTO PhoneticAlphabet VALUES ('G', 'Gulf') INSERT INTO PhoneticAlphabet VALUES ('H', 'Hotel') INSERT INTO PhoneticAlphabet VALUES ('I', 'India') INSERT INTO PhoneticAlphabet VALUES ('J', 'Juliett') INSERT INTO PhoneticAlphabet VALUES ('A', 'Alfa') INSERT INTO PhoneticAlphabet VALUES ('B', 'Brave') INSERT INTO PhoneticAlphabet VALUES ('C', 'Charlie') INSERT INTO PhoneticAlphabet VALUES ('D', 'Delta') INSERT INTO PhoneticAlphabet VALUES ('E', 'Echo') INSERT INTO PhoneticAlphabet VALUES ('F', 'Foxtrot') INSERT INTO PhoneticAlphabet VALUES ('G', 'Gulf') INSERT INTO PhoneticAlphabet VALUES ('H', 'Hotel') INSERT INTO PhoneticAlphabet VALUES ('I', 'India') INSERT INTO PhoneticAlphabet VALUES ('J', 'Juliett') --See table before deletion SELECT * FROM PhoneticAlphabet ORDER BY Alphabet; --Display what the table will look like when we sort it by Alphabet and CodeWord --and use the row_number function over the dataset see http://technet.microsoft.com/en-us/library/ms186734.aspx --for infromation of row_number function. SELECT Alphabet , CodeWord , row_number() over (partition by Alphabet, CodeWord order by Alphabet) as rn FROM PhoneticAlphabet; --Create a Common Table Expression and delete all rows which value greater than 1 WITH PhoneticAlphabetWithRows AS ( SELECT Alphabet , CodeWord , row_number() over (partition by Alphabet, CodeWord order by Alphabet) as rn FROM PhoneticAlphabet ) DELETE PhoneticAlphabetWithRows where rn > 1; --See the table after deletion select * from PhoneticAlphabet ORDER BY Alphabet; |
Method 2:
This method should work on all versions for SQL Server. It basically works by copying the unique rows out of the original table into a temporary table, and then deleting all records from the original table; the unique rows from the temporary table are then copied into the original table.
Link to SQL Fiddle is here.
— Create Table
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
CREATE TABLE PhoneticAlphabet (Alphabet char(1), CodeWord varchar(20)) -- Load Sample Data in Table INSERT INTO PhoneticAlphabet VALUES ('A', 'Alfa') INSERT INTO PhoneticAlphabet VALUES ('B', 'Brave') INSERT INTO PhoneticAlphabet VALUES ('C', 'Charlie') INSERT INTO PhoneticAlphabet VALUES ('D', 'Delta') INSERT INTO PhoneticAlphabet VALUES ('E', 'Echo') INSERT INTO PhoneticAlphabet VALUES ('F', 'Foxtrot') INSERT INTO PhoneticAlphabet VALUES ('G', 'Gulf') INSERT INTO PhoneticAlphabet VALUES ('H', 'Hotel') INSERT INTO PhoneticAlphabet VALUES ('I', 'India') INSERT INTO PhoneticAlphabet VALUES ('J', 'Juliett') INSERT INTO PhoneticAlphabet VALUES ('A', 'Alfa') INSERT INTO PhoneticAlphabet VALUES ('B', 'Brave') INSERT INTO PhoneticAlphabet VALUES ('C', 'Charlie') INSERT INTO PhoneticAlphabet VALUES ('D', 'Delta') INSERT INTO PhoneticAlphabet VALUES ('E', 'Echo') INSERT INTO PhoneticAlphabet VALUES ('F', 'Foxtrot') INSERT INTO PhoneticAlphabet VALUES ('G', 'Gulf') INSERT INTO PhoneticAlphabet VALUES ('H', 'Hotel') INSERT INTO PhoneticAlphabet VALUES ('I', 'India') INSERT INTO PhoneticAlphabet VALUES ('J', 'Juliett') --Lets see the duplicated records SELECT * FROM PhoneticAlphabet; --Insert the distinct rows from the duplicate rows table to a new temporary table. SELECT DISTINCT * INTO #tmp FROM [PhoneticAlphabet]; --Delete all the rows from the original table DELETE FROM PhoneticAlphabet; --Copy the distinct rows from the temporary table back into the original table (which was empty). INSERT INTO PhoneticAlphabet SELECT * FROM #tmp; DROP TABLE #tmp; --Lets now see the table without duplicates SELECT * FROM PhoneticAlphabet; |
