How to create stored procedure for deleting duplicate rows.

Posted by Sathiya_Narayanan under Sql Server on 10/5/2012 | Points: 10 | Views : 8442 | Status : [Member] | Replies : 4
Hi,

In database having n number of same rows containing all data same.
we have to store only one data reset all same rows to be deleted.

please in this to create sp.

SATHIYANARAYANAN


Responses

Posted by: Jayakumars on: 10/5/2012 [Member] [MVP] Bronze | Points: 25

Up
0
Down
hi
try this


Create Proc sp_DeleDuplicates
@PubName varchar(30)
as
Delete top(1) FROM dbo.publishers
WHERE pub_id IN (SELECT MIN(pub_id)
FROM dbo.publishers Where pub_name=@PubName GROUP BY Pub_name,city)


Mark as Answer if its helpful to you

Sathiya_Narayanan, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sathiya_Narayanan on: 10/5/2012 [Member] Starter | Points: 25

Up
0
Down
k when deleting a row that deleted row should move to tempdb database how to perform it.

SATHIYANARAYANAN

Sathiya_Narayanan, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sourabh07 on: 10/16/2012 [Member] Starter | Points: 25

Up
0
Down
Please refer the following link to get the answer.......

It was explained with the temperately table creation concept....


http://www.dotnetfunda.com/blogs/sourabh07/3345/how-to-delete-duplicate-rows-in-sql-table

Please mark as an Answer to help for others in same...problem

Sourabh07

Sathiya_Narayanan, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: CGN007 on: 10/19/2012 [Member] Silver | Points: 25

Up
0
Down
The logic is like this

WITH employeeCTE AS 


(

SELECT ROW_NUMBER() OVER (PARTITION BY emp_name ORDER BY emp_id) AS count_no FROM employee

)

DELETE FROM employeeCTE WHERE count_no > 1


Please implement this as SP...

Sathiya_Narayanan, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response