Importing data from one MySQL dbto another MySQL Db?
Can you please tel开发者_开发问答l me how to import data from one MySQL Db to another MySQL DB. Data schema of both databases is totally different, the second database is the normalized version of first database.
Thanks in advance
I think that the better way is to use a ETL software.
According to Wikipedia:
Extract, transform and load (ETL) is a process in database usage that involves :
- Extracting data from outside sources
- Transforming it to fit operational needs (which can include quality levels)
- Loading it into the end target (database or data warehouse)
You're talking about extract, transform, load (ETL) scripts.
Going from denormalized data to normalized data is hell. I hope you're doing the reverse - normalized to denormalized - which goes like this:
Write a shell script that:
- Dumps the first database:
mysqldump olddb >dump.sql
- Loads it into a clean new database:
mysql newdb <dump.sql
- Write a bunch of
CREATE TABLE AS SELECT ...
commands and run them through mysql to create the denormalized data. - Drop whatever tables you don't care about.
Turning denormalized data into normalized is insanely difficult, requiring an approach that is specific to the dataset you have.
I agree what Stephen says, but you can achive it whith a similar technique. But you have to create more elaborated queries. You can do something like:
INSERT INTO table1(field1, field2,...) SELECT field4,field1,... FROM table1;
Obviously you have to do it in the correct order, IMO this is a good approach so you can create complex queries with joins, etc.
精彩评论