Introduction
The code explained here will show how to delete duplicate entries from a data store, while leaving a single copy. The code will first create a temp table with duplicated records for the field 'FullName
' and then get the IDs of the record which must be deleted and then delete those records.
Using the code
Here is the complete SQL code:
IF OBJECT_ID('TempDup') IS NOT NULL
DROP TABLE 'TempDup'
GO
CREATE TABLE [dbo].[TempDup]
(
[ID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_TempDup_ID] DEFAULT (newid()),
[FullName] [nchar](10) NOT NULL,
CONSTRAINT [PK_TempDup] PRIMARY KEY CLUSTERED ( [ID] ASC )ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO TempDup VALUES ( NEWID(), 'N1')
INSERT INTO TempDup VALUES ( NEWID(), 'N2')
INSERT INTO TempDup VALUES ( NEWID(), 'N2')
INSERT INTO TempDup VALUES ( NEWID(), 'N2')
INSERT INTO TempDup VALUES ( NEWID(), 'N3')
INSERT INTO TempDup VALUES ( NEWID(), 'N3')
SELECT [ID], [FullName], [RowIndex]
FROM
(
SELECT
[ID], [FullName], RANK() OVER (PARTITION BY [FullName]
ORDER BY [ID] ASC) AS [RowIndex]
FROM [dbo].[TempDup]
)[T1]
WHERE [T1].[RowIndex] > 1
GO
DELETE FROM [dbo].[TempDup] WHERE [ID] IN
(
SELECT [ID] FROM
(
SELECT
[ID], [FullName],
RANK() OVER (PARTITION BY [FullName] ORDER BY [ID] ASC) AS [RowIndex]
FROM [dbo].[TempDup]
)[T1] WHERE [T1].[RowIndex] > 1
)
GO