Answer / kishor kumar

This can be solved by 2 Ways

One way

Create unique index on this Table with ignore duplicate row

2nd way.

1st transfer the data to a temp table bu using Distinct
clause then truncate the EMP table and then transfor the
data from temp table to EMP Table.

select distinct (column names) in to #temp from EMP

Truncate table EMP

INSERT INTO EMP

SELECT * FROM #temp

Answer / soorai ganesh

Hey dude, If u use SQLSERVER 2005 This will help u.


;WITH tmpEmp AS
(
SELECT ROW_NUMBER() OVER(PARTITION BY EmpID ORDER BY EmpID)
AS RowNumber, * FROM EMP
)

DELETE FROM tmpEmp WHERE RowNumber> 1

Answer / siddharth

DECLARE @int as int
DECLARE @cnt as int
WHILE EXISTS (SELECt top 1 EmpId from Emp group by EmpId having count(EmpId)>1 order by EmpId)
Begin
SELECT top 1 @int=EmpId,@cnt=Count(EmpId) from Emp group by EmpId having count(EmpId)>1 order by EmpId
Delete top (@cnt-1) from Emp where EmpId = @int
End

Select EmpId,Ename,Sal,DeptId from Emp order by EmId

Answer / shankar

First Of All Create Table
create table EMP(empid int, ename varchar(10),sal int,
deptid int)

insert into EMP values(11, 'Ram', 10000, 10)
insert into EMP values(11, 'Ram',10000,10)
insert into EMP values(22, 'Raj', 20000, 20)
insert into EMP values(22, 'Raj', 20000, 20)
insert into EMP values(33, 'Anil', 15000, 30)
insert into EMP values(33, 'Anil', 15000, 30)
insert into EMP values(44,'bbb',11111,40)
--Now Run This query It works
Declare @empID int, @RecCount int,@Name varchar (10),@Sal
int, @deptid int
SET @RecCount = 0
Declare MyCur Cursor for
Select empid,ename Sal,DeptID,Count(ID)-1 as Records from
EMP Group BY empid,ename Sal,DeptID having Count(empid)>1
Open MyCur
Fetch FROM MyCur INTO @empID,@Name,@sal,@DeptID,@RecCount

WHILE @@FETCH_STATUS = 0
BEGIN
SET ROWCOUNT @RecCount

DELETE from EMP where empID= @empID
SET @empID= 0
SET @Name = ''
SET @Sal= 0
SET @DeptID = 0
SET @RecCount = 0
SET ROWCOUNT 0
Fetch FROM MyCur INTO @empID,@Name,@sal,@DeptID,@RecCount
END

Close MyCur
DEallocate MyCur

Answer / aashish lad

-- Here Temp1 is Temporary Table So it will take All records
-- From Mytable With RowNumbar column
-- We can Delete the Record from


SELECT ROW_NUMBER() OVER(PARTITION BY empid ORDER BY empid)
AS RowNumber, * into #temp1 FROM mytable

DELETE FROM #temp1 WHERE RowNumber> 1

INSERT INTO mytable

SELECT * FROM #temp

Answer / smitha

;with empctc(empid,ename,sal,deptid,ranking)
as
(Select empid,ename,sal,deptid,ranking=Dense_rank() over (
partition by empid,ename,sal,deptid order by NEWID() asc)
from emp
)
delete * from empctc where ranking>1

Answer / guest

WITH T1 AS (
SELECT ROW_NUMBER () OVER ( PARTITION BY EmpId ORDER BY
EmpId) AS RNUM,EmpId FROM EMP)
delete
FROM T1 WHERE RNUM > 1

Answer / vijay

I have tried this query in my PC.Very simply I was able to
remove all duplicate rows.
Please try below query it will work out.

SELECT DISTINCT*FROM EMP(TableName);

You will get below table
EmpId Ename Sal DeptId
11 Ram 10000 10
22 Raj 20000 20
33 Anil 15000 30


Suppose if you have different ENAME but you want only
selected ENAME without duplicate records.
Where We can use below query.

SELECT DISTINCT*FROM EMP WHERE ENAME IN('RAM','RAJ','ANIL');

Please correct me if I am wrong.

Answer / vikas kant

select Distinct * into #Temp_Temp1 from Emp
truncate table Emp
insert into Emp select * from #Temp_Temp1
select * from Emp

Answer / mohan

create table #temp(empid int, ename varchar(10),sal int,
deptid int)

insert into #temp values(11, 'Ram', 10000, 10)
insert into #temp values(11, 'Ram',10000,10)
insert into #temp values(22, 'Raj', 20000, 20)
insert into #temp values(22, 'Raj', 20000, 20)
insert into #temp values(33, 'Anil', 15000, 30)
insert into #temp values(33, 'Anil', 15000, 30)
insert into #temp values(44,'bbb',11111,40)

select * from #temp

set rowcount 1
delete from #temp where empid in(select empid from #temp
group by empid having count(*)>1)
while @@rowcount>0
begin

delete from #temp where empid in(select empid from #temp
group by empid having count(*)>1)
end
set rowcount 0

select * from #temp

Why de-normalization required?

0 Answers  

What is SubQuery in SQL Server 2008

0 Answers   HCL,

How to give a user the option of importing Excel and a delimited text file into a SQL Server Database without manually using SQL DTS?

1 Answers   GE,

what is denormalization and when would you go for it? : Sql server database administration

0 Answers  

What are the advantages of mirroring?

0 Answers  

What is raid? : SQL Server Architecture

0 Answers  

How to list all columns in a table using odbc_columns()?

0 Answers  

What are temporal tables in sql server 2016?

0 Answers  

What are the advantages of using third-party tools?

0 Answers  

Difference between LEN() and DATALENGTH() in sql server ?

0 Answers   HCL,

What is the difference between truncate and delete commands?

0 Answers  

what is cluster and nin-cluster index?

4 Answers