Help Finding and Update/Dupes in 2 Tables

Being fairly new to SQL and SQL scripting, I am at a loss on how to proceed
on my issue.
I have a MSDE database with 2 tables that need to modified. I am changing to
a standard 12 digit code in my PATIENTS table for the field sChartCode. That
code will be in the form of 110012345678. 1100 will preceed the actualy 8
digit chartcode In the PATIENTS table, the same person may be dupliced many
times using vaiations such as 123456, 12345678, 012345678, 12345678 SMITH,
012345678 SMITH. For each of these records, they are linked to the RECORDS
db using the field lPatientId.
I have already manually updated about 20K records in the RECORDS db which
takes way to many hours of time. New records will be imported at about 10K a
week or so and will be over 100K soon. By the way, the SQL server is on the
way.
What I am looking for is an easier way to find the records that have not been
converted in the PATIENTS db and see if they match one that has already been
converted. If it has, it would need to update all records in the RECORDS db
with the correct updated lPatientId and then delete the duplicate record(s)
from the PATENTS db. If not, it would only need to add '1100...' to the
lPatientId field.
Any help or guidance that can give will be most appreciated.
hi,
dale_durham via droptable.com wrote:
> Being fairly new to SQL and SQL scripting, I am at a loss on how to
> proceed on my issue.
> I have a MSDE database with 2 tables that need to modified. I am
> changing to a standard 12 digit code in my PATIENTS table for the
> field sChartCode. That code will be in the form of 110012345678.
> 1100 will preceed the actualy 8 digit chartcode In the PATIENTS
> table, the same person may be dupliced many times using vaiations
> such as 123456, 12345678, 012345678, 12345678 SMITH, 012345678 SMITH.
> For each of these records, they are linked to the RECORDS db using
> the field lPatientId.
> I have already manually updated about 20K records in the RECORDS db
> which takes way to many hours of time. New records will be imported
> at about 10K a week or so and will be over 100K soon. By the way,
> the SQL server is on the way.
> What I am looking for is an easier way to find the records that have
> not been converted in the PATIENTS db and see if they match one that
> has already been converted. If it has, it would need to update all
> records in the RECORDS db with the correct updated lPatientId and
> then delete the duplicate record(s) from the PATENTS db. If not, it
> would only need to add '1100...' to the lPatientId field.
> Any help or guidance that can give will be most appreciated.
can you please post your actual DDL for the involved tables with some INSERT
INTO sample statament?
what I understood is you do not have a good normalization of your
architecture, but I could misunderstood your word...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply