SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Copy data from one table to another DB

    I have two mySQL databases. I need to somehow copy a few columns from a table in one database to a table in another database. I've never done this before? Any ideas on how?

    Thanks.

  2. #2
    SitePoint Columnist
    Join Date
    Nov 2003
    Location
    Ohio
    Posts
    411
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If it is just a rare exercise of carrying this out - you can dump the table to a sql file and run that sql file against the second database. If this is something you will need to do more frequently - you may want to look at replication on the two servers. Pretty straightforward if you use MySQL Administrator, Navicat or some other admin tool.
    Freelance System Administrator, Researcher, Writer
    Practical Applications
    Open Sourcery "SitePoint's Open Source Blog"

  3. #3
    SitePoint Member
    Join Date
    Feb 2005
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    to do this programatically, checkout if mysql supports the ansi "select into" syntax, (cant remember if this is a T-SQL extension or not) in MS SQL this syntax automatically creates a new table using the field parameters specified in the source table.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    copy a few columns? or copy a few rows?

    the latter is a lot easier
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I'm kinda new at this, so how do I, "checkout if mysql supports the ansi "select into" syntax". And to answer the other question, its definitely a few columns, NOT a few rows.

    Thanks again!
    Convert your dollars into silver coins. www.convert2silver.com

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    how do you check out syntax? on the mysql web site

    (and no, from memory, SELECT INTO isn't supported, but CREATE SELECT is)

    if you are adding columns to an existing table and populating it from a second table, that's an UPDATE, so will there be any mismatches, or are you guaranteeing a strict 1-to-1 equality of primary keys?

    or are the few columns being extracted to create a new table on the fly with just those columns?

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

  7. #7
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    You raise good questions. All I know is that I have several columns in the contacts table which reside in the MAIN database. I want to copy over and insert these into the contacts table, which reside in the SECOND database. I would love to preserve the primary key and auto_increment values if possible. Does this help to understand the situation?

    Thank you!
    Convert your dollars into silver coins. www.convert2silver.com

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by busboy
    I want to copy over and insert these into the contacts table, which reside in the SECOND database.
    does this target table already have columns? are they the same columns as you're extracting?

    you know, it would really help if you posted a table layout of both tables and identified the columns that have to be moved
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •