-- 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
19060 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

What is buffer cash in sql server?

1099

Why do you need a sql server?

1027

what are user defined datatypes? : Sql server database administration

924

What is user-defined multi-statement table-valued function?

998

What are the properties of the transaction?

968

What are ddl triggers and types of ddl trigger?

978

Can you leave a union at any time?

1003

What are the parts of a function?

876

What do you understand by recursive stored procedure?

874

What are the types of joins in sql?

1058

What is inner join? Explain with an example?

1120

What is indexing explain it with an example?

935

What is the default schema of your login session in ms sql server?

996

Define a cross join?

955

What are the aggregate and scalar functions?

993