SH script for copy table from 1 DB to another DB on the same server

DibbiDoc · #1 (**permalink**) 04-21-07, 09:15

Does anyone know of a sh-script (or similar) that can copy a table from 1 database to another database on the same server?

Want to "move" records from table1 in db1 into table2 in db2 (within the same server). Table two has identical table structure, but 1 extra column in which the name of the source site should be put, preferably as a passed-on variable.
Records shall be removed from table1 and put in table2 on a regular basis (cron schedule).
The purpose is both to keep a table from getting too large, and to remove unnecessary content but still have the full history available on a separate database. Will use this script for several sites, moving into table2 on db2, and each record will have the name of the source site in that extra column, so it is possible to filter and query for records per site.

I've done a search for this, but this far I've only been able to find code that copy databases from one server to another.

Any help is appreciated!

guelphdad · #2 (**permalink**) 04-21-07, 20:22

too large is hundreds of millions of records. are you talking that size? and even then it isn't too large, just large.

guelphdad · #3 (**permalink**) 04-21-07, 20:31

Code:

INSERT INTO yourtablename
SELECT
databasename.yourtablename.column1,
databasename.yourtablename.column2,
'yourtablename'
FROM yourtablename

the yourtablename in quotes will just enter a string with the name of the table, or whatever else you put in it.

aschk · #4 (**permalink**) 04-23-07, 05:03

A little edit to what guelphdad has done :

Code:

INSERT INTO `todatabase`.`totablename`
SELECT column1
     , column2
     , 'extracolumnvariable'
FROM `fromdatabase`.`fromtablename`

This allows you to run the query from ANYWHERE inside MySQL, so long as you have the correct permissions to do so.

Internet Services

Web Development

Digital Marketing

Consumer Tech