SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Addict
    Join Date
    Jan 2004
    Location
    Nashville
    Posts
    335
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Moving Tables from One DB to Another

    I am a novice...How do I move tables from one database/table to another database?

    I have phpMyAdmin I figure I use that somehow.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,275
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    phpmyadmin has an Export tab -- play with it

    it will produce an sql text file which contains CREATE TABLE ("structure") and INSERT ("data") statements

    just read that file into the SQL tab for the new database and run it

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Jan 2004
    Location
    Nashville
    Posts
    335
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok, playing was good.

    my tables had a prefix of mos_ and it should be jos_ I now assume I can simple rename those?

  4. #4
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    vewwy Intewesting.

    I tried that a couple of weeks ago and whilst it should have worked, it fell over on the loading order of the tables. Constraints meant that they had to be loaded in a more specific order. Unless ~ wouldn't be surprised ~ I may have made a mistake somewhere.

    bazz

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,275
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by pilotjourney View Post
    I now assume I can simple rename those?
    you can rename them after you create them in the target database, or alternatively you can text edit the sql file with search/replace after extracting from the source database

    the neat thing about mysql as compared with other database systems is that the source code CREATE TABLE and INSERT statements is not a proprietary data format, but plain text and standard SQL -- you can even (with minor modification) use the SQL in a completely different database system
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Wizard bronze trophy Kailash Badu's Avatar
    Join Date
    Nov 2005
    Posts
    2,560
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by IBazz View Post
    vewwy Intewesting.

    I tried that a couple of weeks ago and whilst it should have worked, it fell over on the loading order of the tables. Constraints meant that they had to be loaded in a more specific order. Unless ~ wouldn't be surprised ~ I may have made a mistake somewhere.
    bazz
    You can easily get around the constraints by issuing FOREIGN_KEY_CHECKS=0 at the beginning of import operation. IIRC, phpMyAdmin sets it automatically for you if you select a checkbox before performing the export operation.


Bookmarks

Posting Permissions

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