Page 1 of 1

importing a database

Posted: Thu Apr 06, 2006 2:35 am
by s.dot
Basically I have a pretty large database (it's almost 50 megs when gzipped) that I need to import into another database on my local webserver.

Now, that'd be easy if it was just reading the dump and executing queries. But I need to write a script to transform the current database to fit the new database schema.

I've changed a lot of 'y' and 'n' s to 1s and 0s, omitted several fields, and changed field types on others. So I need to manipulate the information before I import the database.

Obviously the script will do this for me. But my question is, should I connect to the current database, do the operations, then close that connection, open the remote database connection and insert that value, then close it and open the local database again all inside of a loop?

Should I write the queries to a text file, then run that generated dump through mysql?

Posted: Thu Apr 06, 2006 3:02 am
by Christopher
I have found that it is faster and easier to import the data in a table with the old schema but in the new database. I usually call these tables something like "import_mytable". Once the data is imported then you can modify it with a series of SQL statements and sometimes one or more temp tables. I prefer this method because it creates a repeatable process (usually a series of queries in a .sql file).

Re: importing a database

Posted: Thu Apr 06, 2006 8:02 am
by BDKR
scottayy wrote: Obviously the script will do this for me. But my question is, should I connect to the current database, do the operations, then close that connection, open the remote database connection and insert that value, then close it and open the local database again all inside of a loop?
While arborint has a method that is good, you are close to something that will work as well. What you've describe above requires too many "opens" and "closings" of connections. Here are the methods I've used.

1) Open a connection to both systems. Pull info from one DB. Manipulate it. Place it in the other.

2) Open a connection from the first. Pull info from it. Write it to a file. Then later go back and dump the file into the database.

Both have their plusses. The advantage of number 2 is that you can save the information for later use or even for debugging to a certain degree.

Cheers