-- Here Temp1 is Temporary Table So it will take All records -- From Mytab

I have a table EMP in which the values will be like this

EmpId Ename Sal DeptId
11 Ram 10000 10
11 Ram 10000 10
22 Raj 20000 20
22 Raj 20000 20
33 Anil 15000 30
33 Anil 15000 30

I want to delete only duplicate Rows. After Delete I want
the output like this

EmpId Ename Sal DeptId
11 Ram 10000 10
22 Raj 20000 20
33 Anil 15000 30

Question Posted / aashish lad

13 Answers
20004 Views
DELL, I also Faced
E-Mail Answers

Answer Posted / 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

Is This Answer Correct ?

2 Yes

0 No

Post New Answer View All Answers

Please Help Members By Posting Answers For Below Questions

Is it possible in sql table to have more than one foreign key?

1094

What are the fixed server level roles? : sql server security

1227

What is sql azure database?

206

What is a field in a table?

1076

What is the osql utility?

1040

how to trace the traffic hitting a sql server? : Sql server database administration

1958

How does index makes search faster?

1027

How to invoke a trigger on demand?

1155

Can we perform backup restore operation on tempdb?

1079

How to provide column names in insert statements in ms sql server?

1228

Define model database?

1034

What gets stored inside msdb database?

1309

How can you check the level of fragmentation on a table?

1092

What is always encrypted?

1059

Do you know the cursor types?

1206