Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Deleting Duplicate batches of rows Expand / Collapse
Author
Message
Posted Monday, July 21, 2008 10:52 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 21, 2011 9:47 AM
Points: 16, Visits: 47
Comments posted to this topic are about the item Deleting Duplicate batches of rows
Post #538145
Posted Monday, July 21, 2008 11:23 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 2, 2012 2:50 AM
Points: 4, Visits: 24
This code does not give proper results. I tried to work on this but its really untested bad work by the developer.
Post #538154
Posted Thursday, August 21, 2008 5:31 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 21, 2011 9:47 AM
Points: 16, Visits: 47
Ritesh what is it that is not working, can you let me know.
Post #556417
Posted Friday, October 3, 2008 1:19 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, October 21, 2008 1:36 AM
Points: 13, Visits: 10
I think this is a much more efficient example for deleting duplicate rows using a single query and not having to resort to using Cursors:

http://dipakjpatel.blogspot.com/2007/08/delete-duplicate-rows-using-single.html

Regards

Nick
Post #580094
Posted Friday, October 3, 2008 11:45 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, September 26, 2014 1:05 PM
Points: 126, Visits: 67

don't need a temp table. don't need cursors.

you can remove dups in one statement.

cheers.
Post #580485
Posted Friday, October 3, 2008 12:17 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, October 17, 2014 11:07 AM
Points: 2,185, Visits: 1,986
The code worked fine for me. There is more than one way to do this. So if you don't like this way try another but in terms of results this code works.



Francis
Post #580512
Posted Monday, October 6, 2008 1:42 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, October 21, 2008 1:36 AM
Points: 13, Visits: 10
I am sure the code works fine, however the article implied that this is one situation where you have to use cursors - which are very inefficient if you can do it alternatively in a single query:

>>This code works in SQL Server 2005 and above, because it uses the delete top (N) statement. Although using a cursor is not always a good idea but there are situations where we have to use it.

I am sure the cursor method with copying the data to a temporary table is satisfactory for small sets of data. I was surprised however to see it listed as a featured article in SQL Server Central's daily newsletter. When having to regularly run a query against e.g. 1m+ records that may involve several tables, it helps to know if there are more efficient methods. I thought it was worth pointing to a more efficient method that I came across recently as this is not a situation where we have to use cursors.

Regards

Nick
Post #580932
Posted Tuesday, October 7, 2008 2:23 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 8, 2008 9:35 AM
Points: 1, Visits: 2
A much simpler way to remove duplicates would be to use SELECT DISTINCT * from tableName

Post #582176
Posted Wednesday, October 8, 2008 1:23 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, October 21, 2008 1:36 AM
Points: 13, Visits: 10
SELECT DISTINCT * from tableName


While this gives you a list of records without the duplicates, it does not help much in deleting the duplicates. Additional code is still need to do something to remove the duplicates from the original table which this article addresses along with the other suggestions.
Post #582369
Posted Friday, January 2, 2009 11:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 2, 2009 11:25 AM
Points: 1, Visits: 5
Thank you for sharing this. It was my introduction to cursors, and I was able to adapt it to keep my input table intact (by adding top(1) to another table, instead of deleting top @cnt).

Going forward, it does appear the common table expression approach linked by Nick has more promise.

All I wanted was something like 'Ignore row errors on insert'!


Post #629002
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse