Worst Case Cross-DB Table Data Migration 2

[craigfw]

Greetings Everyone,

I have two tables, in two databases on one SQL Server:
GFPB.P(HONE)B(OOK)2004 and CELLPHONES.

I've been asked to ask to intergrate Cellphone numbers from the two tables, CELLPHONES.Phones into the GFPB.PB2004 table.

The table.columns I need to import/intergrate to match up the person with the correct cell phone # are:

CELLPHONES.phones (source db.tables)

Individual (nvarchar(50), NULL): First and last name in one column with a comma seperating them, but not all names are seperated.

Number (nvarchar(50), NULL): area code-number surrounded by double quotes: "208-555-1212"

ID (int, NOT NULL) unique indentity per individual


GFPB.PB2004 (target.db.tables)

Individual (nvarchar(255), NULL): First and Last names seperated by a comma, again not all.

Mobile (varchar(225), NULL): area code-number surrounded by double-quotes, no quotes, and nothing.

NO ID Column (nothing unique to identify anyone)

I'm considering rebuilding the target table and trying to bcp out all the data into work table(s)and then sorting and reloading the data based on a common ID.

Any suggestions?

Thanks, Craig

 

[gcole]

My only suggestion is to strip out commas, spaces, periods et from the First/Last name field and use them to relate. Anything that doesn't match, will have to be looked at manually. I assume that the mobile and cell fields have different numbers in them.

 

[SQLSister]

Your real problem is that there is nothing to tell you whether John Smith in table 1 is the same John Smith as in table2. And if there are two John Smith's which one has the cell number? Names are not unique and cannot be used to match things up. Plus it sounds like you might have dataproblems with the names themselves if the data entry wasn't properly validated. Should James Henry be James, Henry or Henry, James?

Questions about posting. See faq183-874

 

[craigfw]

I agree, these tables having no relationships in them. I'm having to consult with the person who-knows-who and can straighten this out. Luck is with me though, this is an Army base and it is a smaller community, so the social engineering and recognition side of this data can be accounted for.

From what I put in my original post, any techncial suggestions concerning how to proceed. If this was a larger community with tens-of-thousands of records, I'd be in deep.

Thanks again,
Craig

 

[SQLSister]

Since both tables have area codes in them, you can use them as kind of a cross check on the the name matching. Lieklly John Smith in area code 304 is not the same as John Smith in area code 814.

USe the left and right functions to pull out the first and last names as much as possible And charindex and patindex may be helpful too.

Create the field to store the cell number in the other table. JOin the tables together as part of an update statement. That will get you all the exact matches. Something like:

Update table1
set field1 = table2.field2
from table1 inner join table2 on table1.lastname = table2.lastname and table1.firstname = table2.firstname

Then write left join select statements to find the non matching records and see if you can manually match them up.

Any names which are in cell phones that you can't match up, insert as new people. Something like:

insert into table1 (firstname, lastname, cell)
select t2.firstname, t2.lastname, t2.cell from table2 t2 left join table1 on table1.lastname = table2.lastname and table1.firstname = table2.firstname
Where Table1.Lastname is null

Questions about posting. See faq183-874