How to Determine the unique IDs of duplicated records

> This is a common problem with some solution

/************************************************** *********************************
*
* Problem:
* Determine the Duplicated Records in a table using single SELECT.
*
* We shall be using Northwind database, add some duplicate records.
*
* Here we want to know if 2 columns (CompanyName,
* PHone) are duplicated in a table.
*
*
* ShipperID CompanyName Phone
* ---- -------- ------
* 1 Speedy Express (503) 555-9831
* 2 United Package (503) 555-3199
* 3 Federal Shipping (503) 555-9931
* 4 Federal Shipping (503) 555-9931
* 5 Speedy Express (503) 555-9831
* 6 Federal Shipping (503) 555-9931
*
*
*
************************************************** **/

==================================================

SOLUTION 1: Gives me the IDs that are duplicated.

==================================================

SELECT
ShipperID, CompanyName, Phone
FROM
SHIPPERS
WHERE
EXISTS (
SELECT
NULL
FROM
SHIPPERS b
WHERE
b.CompanyName = SHIPPERS.CompanyName
AND b.Phone = SHIPPERS.Phone
GROUP BY
b.CompanyName, b.Phone
HAVING
SHIPPERS.ShipperID < MAX( b.ShipperID )
)

/* ********************
* Output results
********************/

ShipperID CompanyName Phone

---- ------------
--------
1 Speedy Express (503) 555-9831
3 Federal Shipping (503) 555-9931
4 Federal Shipping (503) 555-9931

(3 row(s) affected)

================================================== ===========

SOLUTION 2: Gives me the data which are duplicate but
not the IDs

================================================== ===========

SELECT
CompanyName, Phone
FROM
SHIPPERS
GROUP BY
CompanyName, Phone
HAVING
COUNT(*) > 1

/* ********************
* Output results
********************/

CompanyName Phone
------------ --------
Speedy Express (503) 555-9831
Federal Shipping (503) 555-9931

(2 row(s) affected)anonieko@.hotmail.com wrote:
> > This is a common problem with some solution
> /************************************************** *********************************
> *
> * Problem:
> * Determine the Duplicated Records in a table using single SELECT.
> *
> * We shall be using Northwind database, add some duplicate records.
> *
> * Here we want to know if 2 columns (CompanyName,
> * PHone) are duplicated in a table.
> *
> *
> * ShipperID CompanyName Phone
> * ---- -------- ------
> * 1 Speedy Express (503) 555-9831
> * 2 United Package (503) 555-3199
> * 3 Federal Shipping (503) 555-9931
> * 4 Federal Shipping (503) 555-9931
> * 5 Speedy Express (503) 555-9831
> * 6 Federal Shipping (503) 555-9931
> *
> *
> *
> ************************************************** **/
> ==================================================
> SOLUTION 1: Gives me the IDs that are duplicated.
> ==================================================
> SELECT
> ShipperID, CompanyName, Phone
> FROM
> SHIPPERS
> WHERE
> EXISTS (
> SELECT
> NULL
> FROM
> SHIPPERS b
> WHERE
> b.CompanyName = SHIPPERS.CompanyName
> AND b.Phone = SHIPPERS.Phone
> GROUP BY
> b.CompanyName, b.Phone
> HAVING
> SHIPPERS.ShipperID < MAX( b.ShipperID )
> )
> /* ********************
> * Output results
> ********************/
> ShipperID CompanyName Phone
> ---- ------------
> --------
> 1 Speedy Express (503) 555-9831
> 3 Federal Shipping (503) 555-9931
> 4 Federal Shipping (503) 555-9931
> (3 row(s) affected)
>
> ================================================== ===========
> SOLUTION 2: Gives me the data which are duplicate but
> not the IDs
> ================================================== ===========
>
> SELECT
> CompanyName, Phone
> FROM
> SHIPPERS
> GROUP BY
> CompanyName, Phone
> HAVING
> COUNT(*) > 1
>
> /* ********************
> * Output results
> ********************/
>
> CompanyName Phone
> ------------ --------
> Speedy Express (503) 555-9831
> Federal Shipping (503) 555-9931
> (2 row(s) affected)

Those aren't solutions, they are diagnostics. The solution is to fix
the stupid design of the Shippers table by adding a proper key.

:-)

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--sql

How to deploy a single package of a multiple package solution

Hi,

I have a multiple package solution that I've deployed using the manifest file produced with the development environment. If I need to make a change to a single package, how do I then deploy this package? Is it a case of rebuilding the entire solution and re-running the manifest file, or is there a simpler way?

Any help would be much appreciated, cheers.

Can't you just go into the bin folder of your solution and select the appropriate package then copy this to your desired location?|||

I've experienced some strange behaviour doing this in the past that was solved by redeploying the entire solution using the deployment wizard. So I wasn't sure if there was something going on with the registry that I wasn't appreciating.

Are you confident that simply copying a modified package to the SSIS package store location will work consistently?

|||

To be perfectly honest, I've never used the manifest or deployment utility. All I ever do is copy the package from the bin folder to my release folder. I then use the SQL package store and import from my release folder (although you can do this from your development directory, I like to keep one additional layer of last good build that I can reimport to the sql package store if worst comes to worst).

I do not believe that there is anything modified in the registry at any point in time during package deployment. I could very well be wrong, but from what I have read that is not the case. (NOTE: there is the possibility that you are using registry configurations, which you will need to set up in your new location)

-- From Microsoft SQL Server 2005 Integration Services by Kirk Haselden

"Integration Services provides a utility for moving packages, butfor a moment, let's take a step back and think about the deployment problem. What is it you're trying to accomplish? Is there something in the package, some setting or variable that can't be moved by simply copying the package to another machine? Not really. However, problems arise when you move a package that references external resources that are available on one machine that aren't available on another. For example, no amount of configuration magic is going to help if you attempt to run a package that references a custom task that isn't installed on the destination machine."

|||

Ah, maybe that is the answer, simply use the Import option to load modified packages to the SSIS package store. Thanks for taking the time to reply, much appreciated.