Tuesday, August 13, 2013

T-SQL script to delete the duplicate records in the table




/* Table Schema*/
CREATE TABLE [dbo].[SalesData](
      [FiscalYear] [float] NULL,
      [SalesRegion] [nvarchar](255) NULL,
      [SalesAmount] [float] NULL
) ON [PRIMARY]

GO

/* Inserting Duplicate records*/
INSERT INTO [dbo].[SalesData]([FiscalYear],[SalesRegion],[SalesAmount])VALUES(2008,'USA',10000)
INSERT INTO [dbo].[SalesData]([FiscalYear],[SalesRegion],[SalesAmount])VALUES(2008,'USA',10000)
INSERT INTO [dbo].[SalesData]([FiscalYear],[SalesRegion],[SalesAmount])VALUES(2008,'New Zealand',10000)
INSERT INTO [dbo].[SalesData]([FiscalYear],[SalesRegion],[SalesAmount])VALUES(2008,'New Zealand',10000)
INSERT INTO [dbo].[SalesData]([FiscalYear],[SalesRegion],[SalesAmount])VALUES(2008,'UK',10000)
INSERT INTO [dbo].[SalesData]([FiscalYear],[SalesRegion],[SalesAmount])VALUES(2008,'UK',10000)
INSERT INTO [dbo].[SalesData]([FiscalYear],[SalesRegion],[SalesAmount])VALUES(2008,'USA',10000)
INSERT INTO [dbo].[SalesData]([FiscalYear],[SalesRegion],[SalesAmount])VALUES(2008,'India',10000)
    
GO
/*Select the data from table */
SELECT * FROM SalesData
 
/* Script to delete the duplicate data*/
DELETE Sales FROM 
(SELECT ROW_NUMBER() OVER (PARTITION BY FiscalYear,SalesRegion,SalesAmount
                               ORDER BY FiscalYear) SalesCount 
FROM SalesData) Sales 
WHERE Sales.SalesCount > 1   
/*Select the data from table */
 SELECT * FROM SalesData
 

No comments:

Post a Comment