Well - what if i want to migrate 10 (Adressbook-)DBs into one.
The problem: they all look a bit different:
Adressbook 1: name adress eMail tel Telefax portrait
Adressbook 2: name Company aresss: postalcode Telefon: Fax: E-Mail: Internet:
Adressbook 3: name address tel fax email homepage
all ten look like a bit different. How to treat this migration of ten tables into one big DB!?
Well - i am familiar with phpMyadmin - this helps a bit. But how to treat the db-structure…
I have the data in TSV (tab seperated formate)
Hope i was able to make clear what i want. If i have to be more precise - just lemme know
I have made up my mind: Well i can do this like so:
What if i create ten dbs - out of the ten datasets that i have in TSV-format!
To make it easy i want to explain a solution with only the migration of one old [origin db] - to the new (final) database
Let us say - i have a old db and a new db -Then i take care for the names of the columns.
It should work with a INSERT statement.
INSERT INTO db_new.adressbook
(name, prename, street, postalcode, town)
VALUES
(SELECT name, prename, street, postalcode, ort FROM db_old.adressbook)
In the SELECT-Statement i only have to choose the colums which i want to migrate out of the old Database
I want to try this out! What do you think about this solution
INSERT INTO addressbook (name, address, email tel, fax) VALUES
(SELECT name, address, email, tel, telefax FROM addressbook1)
UNION ALL
(SELECT name, company adress, email, telefon, fax FROM addressbook2)
UNION ALL
as you already have the addresses in tab separated format you can create 10 different tables (or less according to the different formats) and loaf the into the database using load data infile command MySQL :: MySQL 5.1 Reference Manual :: 12.2.6 LOAD DATA INFILE Syntax .
After this you can used the commands posted by dilbertone to create a new table with your new address book format.
I shy away from the unions as that not as simple as multiple table formats for INSERT or UPDATE new address DB.
Any chance of the address books containing the same addresses as in another of the address books and would you need to eliminate duplicate addresses?
I forget the differences between Insert Into and Update and whether you should fill the empty fields with spaces or null values, etc.
many thanks for the input! Your ideas were very interesting. I will try out the way with the data infile command according MySQL :: MySQL 5.1 Reference Manual :: 12.2.6 LOAD DATA INFILE Syntax.
I will start with the work in the next days. I come back and report my findings!
Many thanks so far
db1
on a quick note: Does this mean:
LOAD DATA INFILE INTO TABLE addressbook (name, address, email, tel, fax) COLUMNS TERMINATED BY ' ' LINES TERMINATED BY '
'