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

What is a NOLOCK?

3 Answers   NA,

Where do we use trace frag?

0 Answers  

what is denormalization? : Sql server database administration

0 Answers  

What do you mean by collation recursive stored procedure?

0 Answers  

What happens if null values are involved in arithmetic operations?

0 Answers  

what is sql server? : Sql server database administration

0 Answers  

Do you know the policy based administration feature of sql server 2008?

0 Answers  

You have a table with close to 100 million records recently, a huge amount of this data was updated now, various queries against this table have slowed down considerably what is the quickest option to remedy the situation?

0 Answers  

but what if you have to create a database with two filegroups, one on drive c and the other on drive d with log on drive e with an initial size of 600 mb and with a growth factor of 15%? : Sql server database administration

0 Answers  

Explain some DBCC commands?

6 Answers  

What do you mean by sql server agent?

0 Answers  

What is the difference between commit and rollback?

0 Answers