SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Wizard
    Join Date
    Dec 2004
    Location
    USA
    Posts
    1,407
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    how to combine databases with id fields starting at 1?

    I am combining several databases, each with 20 tables, many, but not all, tables having an auto increment id field starting at 1.

    How can I combine them without manually changing each insert statement?

    Thanks.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,268
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    what does it mean to "combine" databases? and why are you doing it?

    are you saying that you are combining tables as well? if so, you're in for a world of pain...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard
    Join Date
    Dec 2004
    Location
    USA
    Posts
    1,407
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    there are multiple versions of a database, db1, db2,... Each db* has the same tables with the same table structure, but each database contains different data. I need to combine the data from all databases all into one database.

    I've experimented with different phpmyadmin settings with no luck.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,268
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    world of pain...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard
    Join Date
    Dec 2004
    Location
    USA
    Posts
    1,407
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    the longest way around this that I see is table by table, delete the autoincrement field, and create an extended INSERT using phpmyadmin.

    That should allow a valid insert and the key field will automatically be updated.

    But this will take A LOT of time and, as you say, "a world of pain."

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,268
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    in each database, you have 20 tables

    are these tables all unrelated to each other? probably not

    that means, if you change the value of an auto_increment PK, then somewhere, there is some FK that has to be changed too

    if you're lucky, you used InnoDB tables and defined the FKs with ON UPDATE CASCADE, which would mean that all you have to do is update the PKs -- a continent of pain, as opposed to a world of pain

    for example, find the highest PK value used in each database

    then in db2, for example, add 100000 to all the PKs (and the updates will cascade to all the FKs), in db3 add 200000 to all the PKs, and so on

    when this is finished, you can INSERT/SELECT all the tables into one database


    can i ask you again why you want to do this?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard
    Join Date
    Dec 2004
    Location
    USA
    Posts
    1,407
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OUCH, I now remember I had to do that for one of my apps a long time ago. of man, you are right it's going to be a WORLD, not a continent, of pain.

    The client wants to combine all databases. currently they are segregated into sub sites like:
    domain.org/product1/
    domain.org/product2/
    domain.org/product3/

    each with a separate database - this was done prior to my being contract.

    Back to client I go.


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
  •