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?
importing a database
Moderator: General Moderators
importing a database
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
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).
(#10850)
Re: importing a database
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.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?
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