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]

/* Inserting Duplicate records*/

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,‘UK’,10000)

INSERT INTO [dbo].[SalesData]([FiscalYear],[SalesRegion],[SalesAmount])VALUES(2008,‘USA’,10000)

INSERT INTO [dbo].[SalesData]([FiscalYear],[SalesRegion],[SalesAmount])VALUES(2008,‘India’,10000)

/*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

Related Posts