remove duplicates, add PK..need help

I am currently cleaning up a "loose" database, by adding primary keys on a
few particular tables that currently have none.
The Primary key will contain 2 fields, but before I add the pk, I need to
delete any duplicates. There should be none or very few that snuck by the
application, so deleting them is not a concern.
Here is a sample of the CURRENT table format:
CREATE TABLE OrderSalesReps(
OrderID int NOT NULL,
SalesRepID int NOT NULL,
Revenue_1 decimal(14,2) NOT NULL,
Revenue_2 decimal(14,2) NOT NULL
)
So the new Primary Key will be on [OrderID and SalesRepID]
but there may be duplicates that exist currently.
What is a clean query to delete dups before I create the Primary Key.
Thanks in advance.
[Please note: OrderID in this table is a Foreign Key which links to
Orders.OrderID - doesn't matter for this case, but thought I'd mention]Hi Chris,
Which version of SQL Server are you working with?
In SQL Server 2005 the solution is pretty simple and fast:
WITH Dups AS
(
SELECT *,
ROW_NUMBER() OVER(PARTITION BY OrderID, SalesRepID
ORDER BY OrderID, SalesRepID) AS RowNum
FROM OrderSalesReps
)
DELETE FROM Dups WHERE RowNum > 1;
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com
Anything written in this message represents my view, my own view, and
nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
"Chris" <rooster575@.hotmail.com> wrote in message
news:utBY9xXhGHA.3904@.TK2MSFTNGP02.phx.gbl...
>I am currently cleaning up a "loose" database, by adding primary keys on a
>few particular tables that currently have none.
> The Primary key will contain 2 fields, but before I add the pk, I need to
> delete any duplicates. There should be none or very few that snuck by the
> application, so deleting them is not a concern.
> Here is a sample of the CURRENT table format:
> CREATE TABLE OrderSalesReps(
> OrderID int NOT NULL,
> SalesRepID int NOT NULL,
> Revenue_1 decimal(14,2) NOT NULL,
> Revenue_2 decimal(14,2) NOT NULL
> )
> So the new Primary Key will be on [OrderID and SalesRepID]
> but there may be duplicates that exist currently.
> What is a clean query to delete dups before I create the Primary Key.
> Thanks in advance.
> [Please note: OrderID in this table is a Foreign Key which links to
> Orders.OrderID - doesn't matter for this case, but thought I'd mention]
>
>|||Unfortunately, this solution has to work with SQL Server 2000 and 2005.
Thanks
-Chris
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:eMVD1ZYhGHA.4144@.TK2MSFTNGP02.phx.gbl...
> Hi Chris,
> Which version of SQL Server are you working with?
> In SQL Server 2005 the solution is pretty simple and fast:
> WITH Dups AS
> (
> SELECT *,
> ROW_NUMBER() OVER(PARTITION BY OrderID, SalesRepID
> ORDER BY OrderID, SalesRepID) AS RowNum
> FROM OrderSalesReps
> )
> DELETE FROM Dups WHERE RowNum > 1;
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
> www.insidetsql.com
> Anything written in this message represents my view, my own view, and
> nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
>
> "Chris" <rooster575@.hotmail.com> wrote in message
> news:utBY9xXhGHA.3904@.TK2MSFTNGP02.phx.gbl...
>|||First you need to decide on what your rules are for deciding which
duplicate to keep. If you're talking about exact duplicates (i.e., all
column values are identical) then you can select the unique records
into a temporary table, truncate the original table, add the primary
key, then insert the unique records back into the original table (you
could insert back then recreate the PK if you wanted as well). For
example:
SELECT DISTINCT OrderID, SalesRepID, Revenue_1, Revenue_2
INTO tmp_OrderSalesReps
TRUNCATE TABLE OrderSalesReps
ALTER TABLE OrderSalesReps
ADD CONSTRAINT PK_OrderSalesReps PRIMARY KEY CLUSTERED (OrderID,
SalesRepID)
INSERT OrderSalesReps (OrderID, SalesRepID, Revenue_1, Revenue_2)
SELECT OrderID, SalesRepID, Revenue_1, Revenue_2
FROM tmp_OrderSalesReps
--
Of course, if you end up with two rows with identical OrderIDs and
SalesRepIDs, but different Revenues then you need to decide which one
to keep. If it's really only a few rows then you could manually remove
them from your work table before inserting back to the original table.
If you wanted to use the highest revenue values found for an order/rep
(for example) then you might change your SELECT...INTO to something
like:
SELECT OrderID, SalesRepID, MAX(Revenue_1), MAX(Revenue_2)
INTO tmp_OrderSalesReps
FROM OrderSalesReps
GROUP BY OrderID, SalesRepID
HTH,
-Tom.
Chris wrote:
> I am currently cleaning up a "loose" database, by adding primary keys on a
> few particular tables that currently have none.
> The Primary key will contain 2 fields, but before I add the pk, I need to
> delete any duplicates. There should be none or very few that snuck by the
> application, so deleting them is not a concern.
> Here is a sample of the CURRENT table format:
> CREATE TABLE OrderSalesReps(
> OrderID int NOT NULL,
> SalesRepID int NOT NULL,
> Revenue_1 decimal(14,2) NOT NULL,
> Revenue_2 decimal(14,2) NOT NULL
> )
> So the new Primary Key will be on [OrderID and SalesRepID]
> but there may be duplicates that exist currently.
> What is a clean query to delete dups before I create the Primary Key.
> Thanks in advance.
> [Please note: OrderID in this table is a Foreign Key which links to
> Orders.OrderID - doesn't matter for this case, but thought I'd mention]

Remove duplicates within pipeline

I have a situation where we get XML files sent daily that need uploading into SQL Server tables, but the source system producing these files sometimes generates duplicate records in the file. The tricky part is, that the record isn't entirely duplicated. What I mean, is that if I look for duplicates by grouping the key columns, having count(*) > 1, I find which ones are duplicates, but when I inspect the data on these duplicates, the other details in the remaining columns may differ. So our rule is: pick the first record, toss the rest of the duplicates.

Because we don't sort on any columns during the import, the first record kept of the duplicates is arbitrary. Again, we can't tell at this point which of the duplicated records is more correct. Someday down the road, we will do this research.

Now, I need to know the most efficient way to accomplish this in SSIS. If it makes it easier, I could just discard all the duplicates, since the number of them is so small.

If the source were a relational table, I could use a SQL statement to filter the records to remove the duplicates, but since the source is an XML file, I don't know how to filter these out in the pipeline, since the file has to be aggregated to search for dups.

Thanks

Kory

Never mind... I think I found exactly what I needed: The Sort Transform.

-Kory

|||

The only way I can think is to use the sort or aggregate transformations. Have you explore those? Notice that those are full blcoking transformation, so memory usage and performance are things you may want to check.

Rafael Salas

|||

Yes, I thought the sort transform would do the trick- and it did for small files. Files with < 500,000 rows sorted immediately, within 5-10 seconds. Files > 500,000 or so just hung. Looking at task manager, the DTSDebugHost.exe kept climbing and my overall memory consumption was > 5G and I only have 3G total on the server.

I would have thought the performance was linearly decrease, and not go from 10 seconds to indefinite for just 200K rows more.

I've downloaded and installed the Extrasort component, but get an error when I try to put it on the design surface. It complains that it wasn't installed correctly. I've uninstalled and reinstalled it twice. I know NSort is another option, but I really am not needing sorting functionality, just removing duplicates.

SSIS comes with a sample solution that builds a component for removing duplicates, but as far as I can tell, the fields to pick to determine duplicates are the only fields that it passes through the pipeline. I need to remove dups based on 3 fields, but pass through the rest of the fields, like the sort component does.

Any other ideas out there?

Thanks

Kory

|||

Hi I'm having the same problem

does the ssis have the capabilities of seperating the duplicate records ? or still i use the query? can you give me some advice on this ?

KoryS wrote:

I have a situation where we get XML files sent daily that need uploading into SQL Server tables, but the source system producing these files sometimes generates duplicate records in the file. The tricky part is, that the record isn't entirely duplicated. What I mean, is that if I look for duplicates by grouping the key columns, having count(*) > 1, I find which ones are duplicates, but when I inspect the data on these duplicates, the other details in the remaining columns may differ. So our rule is: pick the first record, toss the rest of the duplicates.

Because we don't sort on any columns during the import, the first record kept of the duplicates is arbitrary. Again, we can't tell at this point which of the duplicated records is more correct. Someday down the road, we will do this research.

Now, I need to know the most efficient way to accomplish this in SSIS. If it makes it easier, I could just discard all the duplicates, since the number of them is so small.

If the source were a relational table, I could use a SQL statement to filter the records to remove the duplicates, but since the source is an XML file, I don't know how to filter these out in the pipeline, since the file has to be aggregated to search for dups.

Thanks

Kory

|||What version of ExtraSort are you using and what platform is it running on?

Had no problems running ExtraSort file version 1.0.0.3 (98,304 bytes) on a 32 bit dev platform Win XP SP2 as well as Win2k3 SP1. The SQL Server Build on both is 2153 , which "everyone" running IS should be on at this point. Have been unable to get ExtraSort to run natively on x64.

By default, the component installs to C:\Program Files\Ivolva Digital\ExtraSort Component\ExtraSort.dll.

Remove duplicates within pipeline

I have a situation where we get XML files sent daily that need uploading into SQL Server tables, but the source system producing these files sometimes generates duplicate records in the file. The tricky part is, that the record isn't entirely duplicated. What I mean, is that if I look for duplicates by grouping the key columns, having count(*) > 1, I find which ones are duplicates, but when I inspect the data on these duplicates, the other details in the remaining columns may differ. So our rule is: pick the first record, toss the rest of the duplicates.

Because we don't sort on any columns during the import, the first record kept of the duplicates is arbitrary. Again, we can't tell at this point which of the duplicated records is more correct. Someday down the road, we will do this research.

Now, I need to know the most efficient way to accomplish this in SSIS. If it makes it easier, I could just discard all the duplicates, since the number of them is so small.

If the source were a relational table, I could use a SQL statement to filter the records to remove the duplicates, but since the source is an XML file, I don't know how to filter these out in the pipeline, since the file has to be aggregated to search for dups.

Thanks

Kory

Never mind... I think I found exactly what I needed: The Sort Transform.

-Kory

|||

The only way I can think is to use the sort or aggregate transformations. Have you explore those? Notice that those are full blcoking transformation, so memory usage and performance are things you may want to check.

Rafael Salas

|||

Yes, I thought the sort transform would do the trick- and it did for small files. Files with < 500,000 rows sorted immediately, within 5-10 seconds. Files > 500,000 or so just hung. Looking at task manager, the DTSDebugHost.exe kept climbing and my overall memory consumption was > 5G and I only have 3G total on the server.

I would have thought the performance was linearly decrease, and not go from 10 seconds to indefinite for just 200K rows more.

I've downloaded and installed the Extrasort component, but get an error when I try to put it on the design surface. It complains that it wasn't installed correctly. I've uninstalled and reinstalled it twice. I know NSort is another option, but I really am not needing sorting functionality, just removing duplicates.

SSIS comes with a sample solution that builds a component for removing duplicates, but as far as I can tell, the fields to pick to determine duplicates are the only fields that it passes through the pipeline. I need to remove dups based on 3 fields, but pass through the rest of the fields, like the sort component does.

Any other ideas out there?

Thanks

Kory

|||

Hi I'm having the same problem

does the ssis have the capabilities of seperating the duplicate records ? or still i use the query? can you give me some advice on this ?

KoryS wrote:

I have a situation where we get XML files sent daily that need uploading into SQL Server tables, but the source system producing these files sometimes generates duplicate records in the file. The tricky part is, that the record isn't entirely duplicated. What I mean, is that if I look for duplicates by grouping the key columns, having count(*) > 1, I find which ones are duplicates, but when I inspect the data on these duplicates, the other details in the remaining columns may differ. So our rule is: pick the first record, toss the rest of the duplicates.

Because we don't sort on any columns during the import, the first record kept of the duplicates is arbitrary. Again, we can't tell at this point which of the duplicated records is more correct. Someday down the road, we will do this research.

Now, I need to know the most efficient way to accomplish this in SSIS. If it makes it easier, I could just discard all the duplicates, since the number of them is so small.

If the source were a relational table, I could use a SQL statement to filter the records to remove the duplicates, but since the source is an XML file, I don't know how to filter these out in the pipeline, since the file has to be aggregated to search for dups.

Thanks

Kory

|||What version of ExtraSort are you using and what platform is it running on?

Had no problems running ExtraSort file version 1.0.0.3 (98,304 bytes) on a 32 bit dev platform Win XP SP2 as well as Win2k3 SP1. The SQL Server Build on both is 2153 , which "everyone" running IS should be on at this point. Have been unable to get ExtraSort to run natively on x64.

By default, the component installs to C:\Program Files\Ivolva Digital\ExtraSort Component\ExtraSort.dll.

sql

Remove duplicates within pipeline

I have a situation where we get XML files sent daily that need uploading into SQL Server tables, but the source system producing these files sometimes generates duplicate records in the file. The tricky part is, that the record isn't entirely duplicated. What I mean, is that if I look for duplicates by grouping the key columns, having count(*) > 1, I find which ones are duplicates, but when I inspect the data on these duplicates, the other details in the remaining columns may differ. So our rule is: pick the first record, toss the rest of the duplicates.

Because we don't sort on any columns during the import, the first record kept of the duplicates is arbitrary. Again, we can't tell at this point which of the duplicated records is more correct. Someday down the road, we will do this research.

Now, I need to know the most efficient way to accomplish this in SSIS. If it makes it easier, I could just discard all the duplicates, since the number of them is so small.

If the source were a relational table, I could use a SQL statement to filter the records to remove the duplicates, but since the source is an XML file, I don't know how to filter these out in the pipeline, since the file has to be aggregated to search for dups.

Thanks

Kory

Never mind... I think I found exactly what I needed: The Sort Transform.

-Kory

|||

The only way I can think is to use the sort or aggregate transformations. Have you explore those? Notice that those are full blcoking transformation, so memory usage and performance are things you may want to check.

Rafael Salas

|||

Yes, I thought the sort transform would do the trick- and it did for small files. Files with < 500,000 rows sorted immediately, within 5-10 seconds. Files > 500,000 or so just hung. Looking at task manager, the DTSDebugHost.exe kept climbing and my overall memory consumption was > 5G and I only have 3G total on the server.

I would have thought the performance was linearly decrease, and not go from 10 seconds to indefinite for just 200K rows more.

I've downloaded and installed the Extrasort component, but get an error when I try to put it on the design surface. It complains that it wasn't installed correctly. I've uninstalled and reinstalled it twice. I know NSort is another option, but I really am not needing sorting functionality, just removing duplicates.

SSIS comes with a sample solution that builds a component for removing duplicates, but as far as I can tell, the fields to pick to determine duplicates are the only fields that it passes through the pipeline. I need to remove dups based on 3 fields, but pass through the rest of the fields, like the sort component does.

Any other ideas out there?

Thanks

Kory

|||

Hi I'm having the same problem

does the ssis have the capabilities of seperating the duplicate records ? or still i use the query? can you give me some advice on this ?

KoryS wrote:

I have a situation where we get XML files sent daily that need uploading into SQL Server tables, but the source system producing these files sometimes generates duplicate records in the file. The tricky part is, that the record isn't entirely duplicated. What I mean, is that if I look for duplicates by grouping the key columns, having count(*) > 1, I find which ones are duplicates, but when I inspect the data on these duplicates, the other details in the remaining columns may differ. So our rule is: pick the first record, toss the rest of the duplicates.

Because we don't sort on any columns during the import, the first record kept of the duplicates is arbitrary. Again, we can't tell at this point which of the duplicated records is more correct. Someday down the road, we will do this research.

Now, I need to know the most efficient way to accomplish this in SSIS. If it makes it easier, I could just discard all the duplicates, since the number of them is so small.

If the source were a relational table, I could use a SQL statement to filter the records to remove the duplicates, but since the source is an XML file, I don't know how to filter these out in the pipeline, since the file has to be aggregated to search for dups.

Thanks

Kory

|||What version of ExtraSort are you using and what platform is it running on?

Had no problems running ExtraSort file version 1.0.0.3 (98,304 bytes) on a 32 bit dev platform Win XP SP2 as well as Win2k3 SP1. The SQL Server Build on both is 2153 , which "everyone" running IS should be on at this point. Have been unable to get ExtraSort to run natively on x64.

By default, the component installs to C:\Program Files\Ivolva Digital\ExtraSort Component\ExtraSort.dll.

Remove duplicates using SQL query

I recieve duplicate customer bill data which I want to consolidate using SQL query. How to summarize data using single query...both Qty and Price. If either one of price or qty is -ve then both should be -ve.

Here is example data for one customer.

1 – Only Qty is negative

Product Qty Price

Shirt 2 6.00

Shirt -1 6.00

-

Shirt 1 6.00 Result

2 - Only price is negative

Product Qty Price

Pant 2 6.00

Pant 1 -6.00

-

Pant 1 6.00 Result

Thanks for help!!

Pintoo

Based solely upon the limited information and sample data you provided, this solution will meet your requirements.

However, questions arise. Is the price always the same, if not, which price to return? (ABS() may be useful, so could AVG().)

Code Snippet

DECLARE @.MyTable table
( Product varchar(20),
Qty int,
Price decimal(8,2)
)

INSERT INTO @.MyTable VALUES ( 'Shirt', 2, 6.00 )
INSERT INTO @.MyTable VALUES ( 'Shirt', -1, 6.00 )
INSERT INTO @.MyTable VALUES ( 'Pant', 2, 6.00 )
INSERT INTO @.MyTable VALUES ( 'Pant', 1, -6.00 )

SELECT
Product,
Qty = sum( CASE
WHEN Price < 0 THEN ( Qty * -1 )
ELSE Qty
END
),
Price = max( Price )
FROM @.MyTable
GROUP BY Product

Product Qty Price
-- -- -
Pant 1 6.00
Shirt 1 6.00

|||

Thanks Arnie.

Price is not always same. I have to calculate price by sum(price)/sum(Qty). But how can I use the quantity calculated above to calculate price.

for ex.

SELECT
Product,
Qty = sum( CASE
WHEN Price < 0 THEN ( Qty * -1 )
ELSE Qty
END
),
Price = sum(price*qty)/ sum(qty) caclulated above(how to reuse qty from above query...not sure).

FROM @.MyTable
GROUP BY Product

Thanks

Pintoo

|||

Just the way you expressed it -you were almost there.

Using the ABS() function handles the negative prices and quantities.

Code Snippet

SELECT
Product,
Qty = sum( CASE
WHEN Price < 0 THEN ( Qty * -1 )
ELSE Qty
END
),
Price = ( sum( abs( Price ) * abs( Qty )) / sum( abs( Qty )) )
FROM @.MyTable
GROUP BY Product

|||

How about this query..

DECLARE @.MyTable Table

(

Productvarchar(20),

Qtyint,

Pricedecimal(8,2)

)

INSERT INTO @.MyTable VALUES ( 'Shirt', 2, 6.00 )

INSERT INTO @.MyTable VALUES ( 'Shirt', -1, 6.00 )

INSERT INTO @.MyTable VALUES ( 'Pant', 2, 6.00 )

INSERT INTO @.MyTable VALUES ( 'Pant', 1, -6.00 )

Select

Product

, Sum(Price * Qty/abs(Price))

, Sum(Price * Qty)

From

@.MyTable

Group By

Product

|||

Mani,

With your variation, a negative Price OR Qty adversely effects the ( Price * Qty ) calculation.

Notice what happens when two additional rows are added to the dataset. (Probably not exactly what the OP had in mind...) OnHand quantities might easily be negative or zero, but the average price 'should' reflect the prices used in every transaction and therefore not be negative or zero.

Code Snippet

DECLARE @.MyTable Table
( Product varchar(20),
Qty int,
Price decimal(8,2)
)

INSERT INTO @.MyTable VALUES ( 'Shirt', 2, 6.00 )
INSERT INTO @.MyTable VALUES ( 'Shirt', -1, 6.00 )
INSERT INTO @.MyTable VALUES ( 'Pant', 2, 6.00 )
INSERT INTO @.MyTable VALUES ( 'Pant', 1, -6.00 )

INSERT INTO @.MyTable VALUES ( 'Shirt', -1, 6.00 )
INSERT INTO @.MyTable VALUES ( 'Pant', 2, -6.00 )

Select
Product
, Sum( Price * Qty / abs( Price ))
, Sum( Price * Qty )
From @.MyTable
Group By Product

Product
-- - --
Pant -1.00000000000 -6.00
Shirt .00000000000 .00

remove duplicates

Hi I have a report where I am adding up totals of a group in the the report footer my code for this is

IF {BKGCNT.BKESIZ}= 20 and
{BKGCNT.BKETYP}=["DC","DH","FP","HC","HW","OT"]then
numbervar X:=X +{BKGDTL.BTEQTY}

My problem is that BKGDTL.BTEQTY has duplicates which are also added to the equation giving a false total.

I am new to crystal but is there a code I could use to either delete the duplicate or ensure it is not added.

Any help would be great... thankyou in advance.1 thing I can think of is select distinct records. On the toolbar under database select distinct records. That should help eliminate dups.
That is for CR 9

Hope that helps,

GJ|||Thankyou... I did try this but it didn't work unfortunately...
I will just keep trolling the web to see if I can find something..

cheers

Remove Duplicate

HI All,
I want to remove duplicate records from my table based on nic number. I try to put primray key constraint. But there are many many duplicates so cannot do it can I have a query to remove duplicates..
Thnx
;)
ShaniOriginally posted by shani
HI All,

I want to remove duplicate records from my table based on nic number. I try to put primray key constraint. But there are many many duplicates so cannot do it can I have a query to remove duplicates..

Thnx
;)
Shani

http://www.cleardata.biz/articles/dupes.aspx
http://www.sqlteam.com/item.asp?ItemID=3331|||Originally posted by shani
HI All,

I want to remove duplicate records from my table based on nic number. I try to put primray key constraint. But there are many many duplicates so cannot do it can I have a query to remove duplicates..

Thnx
;)
Shani

read this:
http://www.databasejournal.com/features/mssql/article.php/2235081