Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2012
    Location
    New York
    Posts
    5

    Unanswered: Migrating to different tables / Importing

    Can someone help with a query to import data into certain tables? It should only update but not overwrite if data for that user already exists.

    What I have is 3 different tables that are all related by an ID field. i would like to import most of those table filed to 2 different tables(also would be connected by an ID field).

    1) I would either want to migrate date from a certain table>field to a different table>field where the user ID filed matches.

    Or the longer way...

    2) Export all the data from the 3 different tables that make up all the data and then somehow import that back into the new table fields.

    Any advice or help with query?

    Thanks in advance!

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    If you can give an example of what you are looking for? For example, what is the difference between updating information and overwriting information? Do you mean it should insert new rows but ignore any existing ones?

    If the MySQL server is managing multiple databases and you need to move data between the databases then there would be no need to export/import. Just issues INSERT ... SELECT statements which will copy the database between the databases.

    Again as I say an example will give us more of an idea of what you are trying to achieve.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Oct 2012
    Location
    New York
    Posts
    5
    Hehe, ok after about 6 hours, I think I figured out exactly what fields i would need to move the data from and to.

    I would like to move data from the "From_subscriptions_data" table to the "To_subscriptionlog" table. I specified the default values for field that would not be imported from the "From_subscriptions_data" table.

    The next move is from the one table "From_payments" to two different table called "To_paymenttransaction" and "To_paymentinfo". A lot of the field should just have default data that i specified.

    Below is the chart with the table names and the fields.
    Looks a lot cleaner here: https://docs.google.com/spreadsheet/...W01bWI3ODFhN2c

    To_subscriptionlog ----- < ----- From_subscriptions_data
    subscriptionlogid --------- < ----- id
    subscriptionid ------------ < ----- subid
    userid ------------------- < ----- userid
    pusergroupid ------------ < ----- termid
    status ------------------ < ----- active
    regdate ----------------- < ----- stime
    expirydate -------------- < ----- etime
    importsubscriptionlogid --- < ----- (Set all to "0")


    To_paymenttransaction --- < --- From_payments
    paymenttransactionid ------- < --- (Generate next available number in this column)
    paymentinfoid -------------- < --- (Generate next avaliable number in this column)
    transactionid --------------- < --- txn_id
    state ---------------------- < --- (Set all to "1")
    amount -------------------- < --- mc_gross
    currency ------------------- < --- mc_currency
    dateline -------------------- < --- payment_date
    paymentapiid --------------- < --- (Set all to "1")
    request -------------------- < --- (Set all to "NULL")
    reversed ------------------- < --- (Set all to "0")
    To_paymentinfo -----------<
    paymentinfoid -------------- < --- (Same generated number that goes to the paymentinfoif field in To_paymenttransaction table )
    hash ----------------------- < --- (Set all to "Imported")
    subscriptionid --------------- < --- (Set all to "1")
    subscriptionsubid ------------ < --- ("2" IF above field mc_gross is 4, "1" IF above field mc_gross is 6, "0" IF above field mc_gross is 10)
    userid ---------------------- < --- userid
    completed ------------------ < --- (Set all to "0")


    The link below also has some sample data using a specific record where data was once moved the opposite way but it might be a bit confusing and the above hopefully has enough information.

    https://docs.google.com/spreadsheet/...&rm=full#gid=1

    Again, thanks for all the help!

Posting Permissions

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