StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>There are two cases in this:</p> <ol> <li>The databases are stored in the same server</li> <li>The databases are stored in different servers</li> </ol> <h2>Solution 1: Databases in the same server</h2> <p>You just need to insert the data in the destination table:</p> <pre><code>insert into dbDestination.tblDestination (field1, field2, ...) select ... from dbSource.tblSource </code></pre> <p><em><strong>Notes</em></strong></p> <ol> <li>The <code>select</code> statement must include the fields you need to copy to the destination table.</li> <li>The fields in the <code>select</code> statement must be in the same order as the fields specified in the field list in the <code>insert</code> portion</li> </ol> <h2>Solution 2: Databases in different servers</h2> <p>I would export the data to a plain text file, and then import it. I personally prefer .csv files, but it's up to you.</p> <p>You have two possibilities: To use <code>select... into outfile</code> or to use the system terminal (command window).</p> <p><strong>a. Using <code>select... into outfile</code> and <code>load data</code></strong></p> <ol> <li><p>In the server where <code>dbSource</code> is:</p> <p>select ... from dbSource.tblSource into outfile [your destination file] ...</p></li> <li><p>Copy the file to the destination server.</p></li> <li><p>In the server where <code>dbDestination</code> is:</p> <p>load data local infile [your file] ...</p></li> </ol> <p><em><strong>Notes</em></strong></p> <ol> <li>The fields in the <code>select</code> statement must be in the same order as the fields specified in the field list in the <code>insert</code> portion</li> <li>Check MySQL reference manual for the appropriate usage of <code>select... into outfile</code> and <code>load data...</code></li> </ol> <p><strong>b. Using the terminal</strong></p> <ol> <li><p>In Linux (or other Unix systems), open a terminal window and enter the following command:</p> <p>$ mysql -h [sourceServer] -u [yourUser] -p[yourPassword] dbSource -e"select ..." | sed 's/\t/,/g' > yourDestinationFile.csv</p></li> <li><p>Copy the file to the destination server</p></li> <li><p>Start MySQL console, and use `load data ...``</p></li> </ol> <p><em><strong>Notes</em></strong></p> <ol> <li>The fields in the <code>select</code> statement must be in the same order as the fields specified in the field list in the <code>insert</code> portion</li> <li>The <code>| sed 's/\t/,/g'</code> part converts the output of the mysql query to a .csv file. You can use another separator instead of <code>,</code>. For further reference about <code>sed</code> check <a href="http://lowfatlinux.com/linux-sed.html" rel="noreferrer">http://lowfatlinux.com/linux-sed.html</a></li> <li>The destination file <strong>will have row headers</strong>, so you will need to ignore them when you import the data. Simply add <code>ignore 1 lines</code> at the end of the <code>load data...</code> sentence.</li> </ol> <hr> <p>To copy data from one database to another is a very simple task. Hope this points you in the right direction.</p> <p>A word of advice: Download the reference manual for your MySQL version and keep it at hand. You can find most of your solutions there.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload