Delete duplicate records using this script in MS Sql Server

Hi Friends ,

You can delete duplicate records using this script in MS Sql Server. I write a example below.


Declare @tblEmployee As table 
(
Eid int,
Ename varchar(10)
)  

insert into @tblEmployee values(1,'A')
insert into @tblEmployee values(2,'B')
insert into @tblEmployee values(3,'A')
insert into @tblEmployee values(4,'D')
insert into @tblEmployee values(5,'B')

Select * from @tblEmployee 

Delete From @tblEmployee Where Eid in (

Select Eid from 
(Select * , ROW_NUMBER() Over ( partition by Ename Order by Ename ) As ICount  From @tblEmployee )As Qry Where Qry.ICount > 1
)

Select * from @tblEmployee 






Comments

Post a Comment

Popular posts from this blog

Find procedure and function in SQL Server

ROUTINE_TYPE =   'PROCEDURE' OR     'Function' Select   ROUTINE_TYPE , SPECIFIC_NAME , ROU TINE_DEFINITION FROM      INFORMATION_SCHEMA.ROUTINES WHERE     OBJECTPROPERTY ( OBJECT_ID ( SPECI FIC_NAME ), 'IsMSShipped' )   = 0          AND   ROUTINE_DEFINITION    like   '%prcleave%'          AND   ROUTINE_TYPE = 'PROCEDURE'
Read more

Windows Service Install and Un Install in MS VisualStudio 2010

Hi Friends, You can learn How to Install and Un Install Windows Service using Microsoft Technology. >> 1 .First get the path where InstallUtil.exe exist its Mostly the path is same but differ version  it can be differ C:\Windows\Microsoft.NET\Framework\v4.0.30319\InstallUtil.exe >> 2 .Get the Path where you want to put that Service .exe file (Assume it is on "E:\MyApp\MyTestApp.exe") >> 3 .Command for Install service Run Visual Studio Command Prompt as Run admin if Windows 8 else no need to it and Type this C:\Windows\Microsoft.NET\Framework\v4.0.30319\InstallUtil.exe E:\MyApp\MyTestApp.exe -i >> 4 .Command for Un Install service Run Visual Studio Command Prompt as Run admin if Windows 8 else no need to it and Type this C:\Windows\Microsoft.NET\Framework\v4.0.30319\InstallUtil.exe E:\MyApp\MyTestApp.exe -u >> 5 .In case if you already installed Service and you can see there "services.msc" Service list and you have ...
Read more

Find Store Procedures and Functions in MS SQL Server.

Hi Friends , This article will help you to find Store Procedures and Functions in Microsoft SQL Server. Execute this Script which i mentioned below. ROUTINE_TYPE =  'PROCEDURE' OR    'Function' Select  ROUTINE_TYPE , SPECIFIC_NAME , ROU TINE_DEFINITION FROM     INFORMATION_SCHEMA.ROUTINES WHERE    OBJECTPROPERTY ( OBJECT_ID ( SPECI FIC_NAME ), 'IsMSShipped' )   = 0         AND  ROUTINE_DEFINITION   like   '%PrcName%'         AND  ROUTINE_TYPE = 'PROCEDURE'
Read more