Delete duplicate rows in a table in Sql server

When I am developing I came across to delete duplicate rows in a table. This is solution I have applied. I mentioned it in here coz it may help you.

DECLARE @Table AS TABLE([Id] INT IDENTITY(1 ,1) ,NAME NVARCHAR(50))</code>

INSERT INTO @Table( NAME )

SELECT 'jeevan'

UNION ALL

SELECT 'jeevan'

UNION ALL

SELECT 'Nimal'

UNION ALL

SELECT 'Kuma'

UNION ALL

SELECT 'Kuma'



;WITH cte AS (

SELECT NAME

,ROW_NUMBER() OVER(PARTITION BY C.NAME ORDER BY [Id]) AS [RowId]

FROM   @Table AS C

)


DELETE FROM

FROM   cte

WHERE  [RowId]>1


Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s