How-to remove duplicates from MSSQL server

. 

-- START ===== D:\cas\sfw\sql\sql_dev\Install\DbName\6.Testing\4.DuplicatesRemoval\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](

[DuplicatesTableId] [bigint] IDENTITY(1,1) NOT NULL, --the PK  for DuplicatesTable

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

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

CONSTRAINT [PK_DuplicatesTable] PRIMARY KEY CLUSTERED 

(

[DuplicatesTableId] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 

ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

/* 

<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 ================== D:\cas\sfw\sql\sql_dev\Install\DbName\6.Testing\4.DuplicatesRemoval\1.0..dbo..DuplicatesTable.TableCreate.sql

. 

-- START ===== D:\cas\sfw\sql\sql_dev\Install\DbName\6.Testing\4.DuplicatesRemoval\1.1..dbo..DuplicatesTable.TableInsert.sql

SET NOCOUNT ON;

SET XACT_ABORT ON;

GO

SET IDENTITY_INSERT [dbo].[DuplicatesTable] ON;

BEGIN TRANSACTION;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

SELECT 14, N'ColA1', N'ColB1'

COMMIT;

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

GO

SET IDENTITY_INSERT [dbo].[DuplicatesTable] OFF; 

-- END ================== D:\cas\sfw\sql\sql_dev\Install\DbName\6.Testing\4.DuplicatesRemoval\1.1..dbo..DuplicatesTable.TableInsert.sql

. 

-- START ===== D:\cas\sfw\sql\sql_dev\Install\DbName\6.Testing\4.DuplicatesRemoval\2.0.RemoveDuplicates.Script.sql

SELECT 'SELECT * FROM DuplicatesTable ; '

SELECT * FROM DuplicatesTable ; 

Drop TABLE NonDuplicatesTable ; 

SELECT ColA , ColB 

into NonDuplicatesTable 

from DuplicatesTable

Group by  ColA , ColB 

SELECT 'SELECT * FROM NonDuplicatesTable ; '

SELECT * FROM NonDuplicatesTable ; 

-- END ================== D:\cas\sfw\sql\sql_dev\Install\DbName\6.Testing\4.DuplicatesRemoval\2.0.RemoveDuplicates.Script.sql

. 

-- START ===== D:\cas\sfw\sql\sql_dev\Install\DbName\6.Testing\4.DuplicatesRemoval\all.txt

. 

-- START ===== D:\cas\sfw\sql\sql_dev\Install\DbName\6.Testing\4.DuplicatesRemoval\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](

[DuplicatesTableId] [bigint] IDENTITY(1,1) NOT NULL, --the PK  for DuplicatesTable

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

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

CONSTRAINT [PK_DuplicatesTable] PRIMARY KEY CLUSTERED 

(

[DuplicatesTableId] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 

ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

/* 

<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 ================== D:\cas\sfw\sql\sql_dev\Install\DbName\6.Testing\4.DuplicatesRemoval\1.0..dbo..DuplicatesTable.TableCreate.sql

. 

-- START ===== D:\cas\sfw\sql\sql_dev\Install\DbName\6.Testing\4.DuplicatesRemoval\1.1..dbo..DuplicatesTable.TableInsert.sql

SET NOCOUNT ON;

SET XACT_ABORT ON;

GO

SET IDENTITY_INSERT [dbo].[DuplicatesTable] ON;

BEGIN TRANSACTION;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

SELECT 14, N'ColA1', N'ColB1'

COMMIT;

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

GO

SET IDENTITY_INSERT [dbo].[DuplicatesTable] OFF; 

-- END ================== D:\cas\sfw\sql\sql_dev\Install\DbName\6.Testing\4.DuplicatesRemoval\1.1..dbo..DuplicatesTable.TableInsert.sql

. 

-- START ===== D:\cas\sfw\sql\sql_dev\Install\DbName\6.Testing\4.DuplicatesRemoval\2.0.RemoveDuplicates.Script.sql

SELECT 'SELECT * FROM DuplicatesTable ; '

SELECT * FROM DuplicatesTable ; 

Drop TABLE NonDuplicatesTable ; 

SELECT ColA , ColB 

into NonDuplicatesTable 

from DuplicatesTable

Group by  ColA , ColB 

SELECT 'SELECT * FROM NonDuplicatesTable ; '

SELECT * FROM NonDuplicatesTable ; 

-- END ================== D:\cas\sfw\sql\sql_dev\Install\DbName\6.Testing\4.DuplicatesRemoval\2.0.RemoveDuplicates.Script.sql

. 

-- START ===== D:\cas\sfw\sql\sql_dev\Install\DbName\6.Testing\4.DuplicatesRemoval\all.txt

. 

-- START ===== D:\cas\sfw\sql\sql_dev\Install\DbName\6.Testing\4.DuplicatesRemoval\1.0..d 

-- END ================== D:\cas\sfw\sql\sql_dev\Install\DbName\6.Testing\4.DuplicatesRemoval\all.txt

. 

-- START ===== D:\cas\sfw\sql\sql_dev\Install\DbName\6.Testing\4.DuplicatesRemoval\ConcatenateAll.bat

ECHO EMPTY THE FILE 

ECHO REMOVE THE OUTPUT FILE ALREADY ...

ECHO. >all.txt

ECHO FOR EACH LOG FILE IN THE CURRENT DIRECTORY DO CONTCATENATE IT 

ECHO IN THE ALL.TXT FILE 

for /f %%i in ('dir /s /b /a-d') do echo . >>all.txt&ECHO -- START ===== %%i>>all.txt&type %%i>>all.txt&ECHO. >>all.txt&echo -- END ================== %%i>>all.txt&ECHO. >>all.txt

PAUSE

ECHO OPEN THE ALL.TXT FILE 

all.txt 

-- END ================== D:\cas\sfw\sql\sql_dev\Install\DbName\6.Testing\4.DuplicatesRemoval\ConcatenateAll.bat