Duplicate Removal and Intelligent T-SQL

I am looking for some T_SQL code that would help us eliminate
duplicate data entry into our mailing db. Finding and removing the
existing dups is not rocket science, but I am looking for a complete
solution.

We also want to be able to come up with a T-SQL script to be
intelligent enough to know if:

"Bill Jones" is the same as "Billy Jones" or

"Bill Jones" is the same as "William Jones" or

"Bill Jones at 123 My street" is the same as "Bill Jones at 123 My
St."

Purchasing scripts from a company is not out of the question. Its a
matter of script cost versus my hourly rate to create them... Needless
to say, something cheap.

We have only heard of one app that is dynamic enough to handle this,
and the cost was way, way out of our range, 6 figures is almost 2
years salary.

Any ideas would be greatly appreciated.funkdm1@.yahoo.com (Dave) wrote in message news:<f5174e0f.0406071223.73e8fc2d@.posting.google.com>...
> I am looking for some T_SQL code that would help us eliminate
> duplicate data entry into our mailing db. Finding and removing the
> existing dups is not rocket science, but I am looking for a complete
> solution.
> We also want to be able to come up with a T-SQL script to be
> intelligent enough to know if:
> "Bill Jones" is the same as "Billy Jones" or
> "Bill Jones" is the same as "William Jones" or
> "Bill Jones at 123 My street" is the same as "Bill Jones at 123 My
> St."
> Purchasing scripts from a company is not out of the question. Its a
> matter of script cost versus my hourly rate to create them... Needless
> to say, something cheap.
> We have only heard of one app that is dynamic enough to handle this,
> and the cost was way, way out of our range, 6 figures is almost 2
> years salary.
> Any ideas would be greatly appreciated.

This may help - I've used it successfully to do fuzzy matching of
duplicate company names:

http://www.winnetmag.com/SQLServer/...6094/26094.html

Simon|||Simon,

That was a very interesting article. It also came with about 1200
lines of t-SQL. I dont think thats exactly what I need, but I can
definetely use pieces of it.

Thanks

sql@.hayes.ch (Simon Hayes) wrote in message news:<60cd0137.0406072358.1cb5c387@.posting.google.com>...
> funkdm1@.yahoo.com (Dave) wrote in message news:<f5174e0f.0406071223.73e8fc2d@.posting.google.com>...
> > I am looking for some T_SQL code that would help us eliminate
> > duplicate data entry into our mailing db. Finding and removing the
> > existing dups is not rocket science, but I am looking for a complete
> > solution.
> > We also want to be able to come up with a T-SQL script to be
> > intelligent enough to know if:
> > "Bill Jones" is the same as "Billy Jones" or
> > "Bill Jones" is the same as "William Jones" or
> > "Bill Jones at 123 My street" is the same as "Bill Jones at 123 My
> > St."
> > Purchasing scripts from a company is not out of the question. Its a
> > matter of script cost versus my hourly rate to create them... Needless
> > to say, something cheap.
> > We have only heard of one app that is dynamic enough to handle this,
> > and the cost was way, way out of our range, 6 figures is almost 2
> > years salary.
> > Any ideas would be greatly appreciated.
> This may help - I've used it successfully to do fuzzy matching of
> duplicate company names:
> http://www.winnetmag.com/SQLServer/...6094/26094.html
> Simon|||>> I am looking for some T_SQL code that would help us eliminate
duplicate data entry into our mailing db .. not rocket science, but I
am looking for a complete solution. <<

Yes, it is :) Get a package and save yourself 5-10 man-years.
Call the NCSC at 800-238-3150 and request the following free
publications:

PUB 25 - Designing Business Letter Mail
PUB 28 - Postal Addressing Standards
PUB 221 - Addressing for Success

Buy a mailing list product to scrub your data. It is much harder than
it looks. Here is an old list of companies that sell ZIP code and
mailing related product:

BCC Software Inc.
39 Saginaw Drive #630
Rochester, NY 14623

Comco, Inc.
2211 Grant Street
Box #349
Bettendorf, IA 52722

CD Light
8861 South Silverstone Way
Sandy, UT 84093
CD-Rom of ZIP+4 for $30

Dassgar Systems
Box #915
Bennington, VT 05201-0915
Global-Z; international postal codes for lots of places

Eric Isaacson Software
416 East Bloomington
Bloomington, IN 47401-4739
demo version of ZipKey

Group 1 Software
4200 Parliament Place #600
Lanham, MD 20706

Melissa Data Corporation
32122 Paseo Adelanto
San Juan Capistrano, CA 92675

Search Software America
1445 East Putnam Ave
Old Greenwich, CT 06870

(ask for their booklet "The Math, Myth & Magic of Name Searching and
Matching". It is quite informative)

For 'UK postcode databases', the Royal Mail holds the copyright and
will licence you an 'outwards' database (i.e. the first segment of the
full postcode) for about 150 a year, or an 'inwards' database (i.e.
the full 9- or 10-character postcode) for about 500 UK pounds per
year.

KJL Software (www.kjlsoftware.com) gives you 5-digit ZIP Code, City,
State,USPS Status Code, LATEST Area Code(s) from NANPA, Time
Zone,Latitude, and Longitude for all valid US Postal Service 5 digit
ZIP Codes/City/State combinations and a Distance Calculator.|||I'd also recommend http://www.qas.com/

We use them for address checking/changing and there is additional
functionality (in the UK at least) to check against the electoral role
to verify if someone is at a specific address.

Failing that, UK wise, Experian or Equifax may be of some help. I'm
not sure what they do outside the UK though.

Hope that helps.

dup check while import from excel

There have been some pretty advanced stuff here I've gone through, but I am
a
.NET programmer by trade, so T-SQL is a bit different for me.
I have an outside list of items with ID codes that I need to import to an
existing table, but I need to have the procedure check for dups in the excel
list BEFORE inserting them, if the ID already exists in the database, then
skip and move onto the next one.
(See the OOP concept here?)
I was thinking about something along the line of an If Exist followed by a
skip record or insert, but I think I am making this too complicated.
can someone give me a down-to-earth example of how to accomplish this?
Environment:
From: Excel File named InItem
ID field: ItemID
to:
SQL Database named dbo.tblInItem field ItemID
Thank you for any assistance...
-Tobias Mazzei
Wraith SystemsI prefer adding a linked server to the excel file (look up sp_addlinkedserve
r
in Books Online) to bring the two data sources to common ground.
Building an INSERT query is pretty simple from there:
insert <SQL_table>
(
<destination_column_list>
)
select <source_column_list>
from <Excel_table>
where (not exists (
..the excluding query is
dependent on the actual data
..basically you need to list
items that already exist in the destination table
))
For better help, please post DDL and sample data.
ML|||This is ussually how I check if a record already exsists, you can change the
creteria
IF EXISTS (SELECT 'True' FROM tb_Alerts WHERE ID = @.AlertID)
BEGIN
UPDATE tb_Alerts
SET AlertName = @.AlertName
WHERE ID = @.AlertID;
SELECT @.AlertID;
END
ELSE
BEGIN
INSERT INTO tb_Alerts (AlertName)
VALUES (@.AlertName)
SELECT @.@.IDENTITY;
END
-Mark
"Wraith Systems" <WraithSystems@.discussions.microsoft.com> wrote in message
news:AD088624-7F0F-4F88-BD8C-5C0AE09F754D@.microsoft.com...
> There have been some pretty advanced stuff here I've gone through, but I
> am a
> .NET programmer by trade, so T-SQL is a bit different for me.
> I have an outside list of items with ID codes that I need to import to an
> existing table, but I need to have the procedure check for dups in the
> excel
> list BEFORE inserting them, if the ID already exists in the database, then
> skip and move onto the next one.
> (See the OOP concept here?)
> I was thinking about something along the line of an If Exist followed by a
> skip record or insert, but I think I am making this too complicated.
> can someone give me a down-to-earth example of how to accomplish this?
> Environment:
> From: Excel File named InItem
> ID field: ItemID
> to:
> SQL Database named dbo.tblInItem field ItemID
> Thank you for any assistance...
> --
> -Tobias Mazzei
> Wraith Systems
>|||I am getting closer. Thanks for the response, I tried looking up "not exists
"
in the help files, and with the lack of definition, it brought me here.
where (not exist (exceltableID=SQLtableID))
This was my initial concept, but it didn't look like SQL understood what I
was trying to tell it, the words not and exists were greyed.
So the concept was to select the items where the SQLtable did not have (or
equal to) what was the current record the datareader was at, right?
is this accurate useage? and can this be done in a stored procedure?
-Tobias Mazzei
Wraith Systems
"ML" wrote:

> I prefer adding a linked server to the excel file (look up sp_addlinkedser
ver
> in Books Online) to bring the two data sources to common ground.
> Building an INSERT query is pretty simple from there:
> insert <SQL_table>
> (
> <destination_column_list>
> )
> select <source_column_list>
> from <Excel_table>
> where (not exists (
> ...the excluding query is
> dependent on the actual data
> ...basically you need to list
> items that already exist in the destination table
> ))
>
> For better help, please post DDL and sample data.
>
> ML|||On Mon, 7 Nov 2005 15:09:02 -0800, Wraith Systems wrote:

>I am getting closer. Thanks for the response, I tried looking up "not exist
s"
>in the help files, and with the lack of definition, it brought me here.
>where (not exist (exceltableID=SQLtableID))
>This was my initial concept, but it didn't look like SQL understood what I
>was trying to tell it, the words not and exists were greyed.
>So the concept was to select the items where the SQLtable did not have (or
>equal to) what was the current record the datareader was at, right?
>is this accurate useage? and can this be done in a stored procedure?
Hi Tobias,
No, this is not a correct query. The argument to an EXISTS clause should
be a subquery. And in this case, the subquery should be correlated to
the main query.
Try adapting the code below to your tables:
INSERT INTO DestTable (KeyColumn, DataColumn1, DataColumn2)
SELECT s.KeyColumn, s.DataColumn1, s.DataColumn2
FROM SourceTable AS s
WHERE NOT EXISTS (SELECT *
FROM DestTable AS d
WHERE d.KeyColumn = s.KeyColumn)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thank you Hugo. I think Microsoft finally answered the question as well, but
using a <not in> statement. This seems to work as well. If there are
performance factors, I have not found them yet, but I am only inserting
around 1,000 items at a time per batch.
-Tobias Mazzei
Wraith Systems
"Hugo Kornelis" wrote:

> On Mon, 7 Nov 2005 15:09:02 -0800, Wraith Systems wrote:
>
> Hi Tobias,
> No, this is not a correct query. The argument to an EXISTS clause should
> be a subquery. And in this case, the subquery should be correlated to
> the main query.
> Try adapting the code below to your tables:
> INSERT INTO DestTable (KeyColumn, DataColumn1, DataColumn2)
> SELECT s.KeyColumn, s.DataColumn1, s.DataColumn2
> FROM SourceTable AS s
> WHERE NOT EXISTS (SELECT *
> FROM DestTable AS d
> WHERE d.KeyColumn = s.KeyColumn)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||On Wed, 9 Nov 2005 08:46:33 -0800, Wraith Systems wrote:

>Thank you Hugo. I think Microsoft finally answered the question as well, bu
t
>using a <not in> statement. This seems to work as well. If there are
>performance factors, I have not found them yet, but I am only inserting
>around 1,000 items at a time per batch.
Hi Tobias,
There are two things to consider when you use NOT IN with a subquery:
1. If one of the rows in the subquery returns a NULL, the NOT IN will
never evaluate to True. You have to make sure that the subquery can't
return NULLs, else your query will fail.
If you use NOT EXISTS, then you don't have this problem.
2. The NOT IN can only be used if you have a sigle-key column. For a
multi-key column, NOT IN can't be used, since SQL Server doesn't
implement the ANSI standard row constructor.
With NOT EXISTS, the number of columns in the key doesn't matter.
For these reasons, I recommend to always rewrite NOT IN with a subquery
to NOT EXISTS with a subquery. Using NOT IN with a list of constants (as
in "WHERE Status NOT IN ('Sold out', 'Discontinued')") is okay, though.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Here is what I am using currently, but based on your responses, I anticipate
issues.
Insert Into <server>.tblInItemAddDescr
select ItemId,AddlDescr,ts
from xclItemAddDescr...[tblInItemAddDescr$]
where itemid not in(select itemid from <server>.tblInItemAddDescr)
go
I am not sure what multi-key vs. single-key would be considered, would you
be able to give a bit more detail on that?
-Tobias Mazzei
Wraith Systems
"Hugo Kornelis" wrote:

> On Wed, 9 Nov 2005 08:46:33 -0800, Wraith Systems wrote:
>
> Hi Tobias,
> There are two things to consider when you use NOT IN with a subquery:
> 1. If one of the rows in the subquery returns a NULL, the NOT IN will
> never evaluate to True. You have to make sure that the subquery can't
> return NULLs, else your query will fail.
> If you use NOT EXISTS, then you don't have this problem.
> 2. The NOT IN can only be used if you have a sigle-key column. For a
> multi-key column, NOT IN can't be used, since SQL Server doesn't
> implement the ANSI standard row constructor.
> With NOT EXISTS, the number of columns in the key doesn't matter.
> For these reasons, I recommend to always rewrite NOT IN with a subquery
> to NOT EXISTS with a subquery. Using NOT IN with a list of constants (as
> in "WHERE Status NOT IN ('Sold out', 'Discontinued')") is okay, though.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||On Wed, 9 Nov 2005 16:27:57 -0800, Wraith Systems wrote:

>Here is what I am using currently, but based on your responses, I anticipat
e
>issues.
>Insert Into <server>.tblInItemAddDescr
>select ItemId,AddlDescr,ts
>from xclItemAddDescr...[tblInItemAddDescr$]
>where itemid not in(select itemid from <server>.tblInItemAddDescr)
>go
Hi Tobias,
If the column itemid in <server>.tblInItemAddDescr can't be NULL, then
you won't have any issued with this. You couls still compare it to the
corresponding NOT EXISTS version to test for performance differences,
though.
OTOH, if the itemid column can hold NULLs, then you *will* have issues.

>I am not sure what multi-key vs. single-key would be considered, would you
>be able to give a bit more detail on that?
My bad. I messed up when typping. I meant multi-column vs single-column
keys.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)