Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2007
    Posts
    1

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

    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!

  2. #2
    Join Date
    Mar 2004
    Posts
    480
    too large is hundreds of millions of records. are you talking that size? and even then it isn't too large, just large.

  3. #3
    Join Date
    Mar 2004
    Posts
    480
    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.

  4. #4
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •