How-to remove duplicates from MSSQL table ...

. 

-- START === 1.0.dbo..DuplicatesTable.TableCreate.sql

/****** Object:  Table [dbo].[DuplicatesTable]    Script Date: 03/29/2010 21:24:02 ******/

IF  EXISTS (SELECT * FROM sys.objects 

WHERE object_id = OBJECT_ID(N'[dbo].[DuplicatesTable]') AND type in (N'U'))

DROP TABLE [dbo].[DuplicatesTable]

GO

/****** Object:  Table [dbo].[DuplicatesTable]    Script Date: 03/29/2010 21:24:02 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[DuplicatesTable](

[ColA] [varchar](10) NOT NULL, -- the name of the DuplicatesTable

[ColB] [varchar](10) NULL,  -- the description of the e DuplicatesTable 

) 

/* 

<doc> 

Models a DuplicatesTable for 

</doc>

*/

GO

--============================================================ DuplicatesTable START

declare @ScriptFileName varchar(2000)

SELECT @ScriptFileName = '$(ScriptFileName)'

SELECT @ScriptFileName + ' --- DuplicatesTable START =========================================' 

declare @TableName varchar(200)

select @TableName = 'DuplicatesTable'

SELECT 'SELECT name from sys.tables where name =''' + @TableName + ''''

SELECT name from sys.tables 

where name = @TableName

DECLARE @TableCount INT 

SELECT @TableCount  = COUNT(name ) from sys.tables 

where name =@TableName

if @TableCount=1

SELECT ' DuplicatesTable PASSED. The Table ' + @TableName + ' EXISTS ' 

ELSE 

SELECT ' DuplicatesTable FAILED. The Table ' + @TableName + ' DOES NOT EXIST ' 

SELECT @ScriptFileName + ' --- DuplicatesTable END =========================================' 

--============================================================ DuplicatesTable END

GO

-- END ===  1.0.dbo..DuplicatesTable.TableCreate.sql

. 

-- START === 1.1..dbo..DuplicatesTable.TableInsert.sql

BEGIN TRANSACTION;

INSERT INTO [dbo].[DuplicatesTable]([ColA], [ColB])

SELECT   N'ColA', N'ColB' UNION ALL

SELECT N'ColA', N'ColB' UNION ALL

SELECT  N'ColA', N'ColB' UNION ALL

SELECT  N'ColA', N'ColB' UNION ALL

SELECT  N'ColA', N'ColB' UNION ALL

SELECT  N'ColA', N'ColB' UNION ALL

SELECT  N'ColA', N'ColB' UNION ALL

SELECT  N'ColA1', N'ColB1' UNION ALL

SELECT  N'ColA1', N'ColB1' UNION ALL

SELECT  N'ColA1', N'ColB1' UNION ALL

SELECT  N'ColA1', N'ColB1' UNION ALL

SELECT  N'ColA1', N'ColB1' UNION ALL

SELECT  N'ColA1', N'ColB1' UNION ALL

SELECT  N'ColA1', N'ColB1'

COMMIT;

RAISERROR (N'[dbo].[DuplicatesTable]: Insert Batch: 1.....Done!', 10, 1) WITH NOWAIT;

GO

-- END ===  1.1..dbo..DuplicatesTable.TableInsert.sql

. 

-- START === 2.0.RemoveDuplicates.Script.sql

ALTER TABLE dbo.DuplicatesTable ADD

DuplicatesTableId int NOT NULL IDENTITY (1, 1)

GO

-- Then the delete is trivial:

DELETE FROM dbo.DuplicatesTable WHERE DuplicatesTableId NOT IN 

(SELECT MAX(DuplicatesTableId) FROM dbo.DuplicatesTable GROUP BY ColA , ColB)

Select * from DuplicatesTable ;  

-- END ===  2.0.RemoveDuplicates.Script.sql