Delete duplicate records in SQL Server

Declare @tbl As table 
(
iid int,
iname varchar(10)
)  

insert into @tbl values(1,'A')
insert into @tbl values(2,'B')
insert into @tbl values(3,'C')
insert into @tbl values(4,'A')
insert into @tbl values(5,'C')

Select * from @tbl 

Delete From @tbl Where iid in (

Select iid from 
(Select * , ROW_NUMBER() Over ( partition by iname Order by iname ) As ICount  From @tbl)

As Qry Where Qry.ICount > 1
)

Select * from @tbl

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