Jump to content
  • Announcements

    • alpenwasser

      Please Use CODE Tags   31 Jan 2016

      Welcome to the Programming and Software Design Section,

      When asking for help with programming issues, please use the code tags to enclose your code, it makes things much more easily readable for the people trying to help you, thus improving your chances of actually getting help.
        To add code tags, click the <> button on the editor toolbar, then enter your code in the code editor that appears. If you are on a mobile device, or prefer to use BBCode, you can use [code] // Your code here // It will be syntax highlighted, though not necessarily corectly. [/code] (but the code editor is more consistent and less buggy).
Joveice

Merge two SQL Databases and keep "pivot" id

Recommended Posts

Posted · Original PosterOP

Hello, so I have two databases I would like to merge.

 

Under are examples on data that will need to be merged

 

Data 1

 

| id | name  |                        | id | pr_id | user_id |             | id | product   |

| -- | ----- |                        | -- | ----- | ------- |             | -- | --------- |

| 1  | Alex  |                        | 1  | 1     | 2       |             | 1  | A product |

| 2  | Scott |                        | 2  | 3     | 1       |             | 2  | Product 2 |

| 3  | Alan  |                        | 3  | 2     | 3       |             | 3  | Something |

 

And data 2

 

 

| id | name  |                        | id | pr_id | user_id |             | id | product   |

| -- | ----- |                        | -- | ----- | ------- |             | -- | --------- |

| 2  | Jack  |                        | 2  | 3     | 2       |             | 3  | A product |

 

As you see these will conflict, is there a way to merge them? or am I best of doing this with a script?

 

at the end I would want this

 

| id | name  |                        | id | pr_id | user_id |             | id | product   |

| -- | ----- |                        | -- | ----- | ------- |             | -- | --------- |

| 1  | Alex  |                        | 1  | 1     | 2       |             | 1  | A product |

| 2  | Scott |                        | 2  | 3     | 1       |             | 2  | Product 2 |

| 3  | Alan  |                        | 3  | 2     | 3       |             | 3  | Something |

| 4  | Jack  |                        | 4  | 4     | 4       |             | 4  | A product |

Link to post
Share on other sites

Well, the most basic solution would be to go table by table and compare IDs of each product with the one in the first database and change it if needed, then propagate the change in the other tables. Then you could just merge.

 

For example... start with the last table

You have "A product" which conflicts with "Something" in the first database. You know there's 3 products in the first database so this item needs id 4  , and repeat with next row in table and if needed give id 5 , 6, 7 etc 

So you alter that table of the second database and make it    id  , new_id, product    and you'll have  3, 4 , a product

if the id doesn't need to change, just copy it to new_id unchanged

 

then you can go do something like this select * from products where new_id>id  and you get only the products for which you had to change id.

so now you can go in middle table and do something like update tablename set pr_id = [new_product_id] where pr_id = [old_product_id]

 

then repeat with usernames and other tables and so on.

 

When you're done, just select all rows from table with new_id > 3 (last id) and insert them one by one in your original table.

 

 

 

Link to post
Share on other sites
Posted · Original PosterOP
5 minutes ago, ImNotThere said:

what are the primary keys? 

you have 2 ID=2 with 2 different names, it'd be easier to use an INSERT statement

id is the primary.

 

2 minutes ago, mariushm said:

Well, the most basic solution would be to go table by table and compare IDs of each product with the one in the first database and change it if needed, then propagate the change in the other tables. Then you could just merge.

 

For example... start with the last table

You have "A product" which conflicts with "Something" in the first database. You know there's 3 products in the first database so this item needs id 4  , and repeat with next row in table and if needed give id 5 , 6, 7 etc 

So you alter that table of the second database and make it    id  , new_id, product    and you'll have  3, 4 , a product

if the id doesn't need to change, just copy it to new_id unchanged

 

then you can go do something like this select * from products where new_id>id  and you get only the products for which you had to change id.

so now you can go in middle table and do something like update tablename set pr_id = [new_product_id] where pr_id = [old_product_id]

then repeat with usernames and so on.

 

When you're done, just select all rows from table with new_id > 3 (last id) and insert them one by one in your original table.

 

 

There will be around 20k ~ records in each table I'm not planning to go and change them manually.

 

Any better options?

Link to post
Share on other sites
Just now, Joveice said:

id is the primary.

 

There will be around 20k ~ records in each table I'm not planning to go and change them manually.

 

Any better options?

set up a simple relationship? and use INNER JOIN where ID is equal to output records?

Link to post
Share on other sites

Well of course not, write some script in php or some language , it's just a bunch of queries and some foreach  or while or whatever

 

If you're willing to share the databases, maybe just a small set of values, i may be willing to give it a shot in writing a script or something if i don't get bored

 

ex.

 

get last id from the table products from first database.  store it in some variable

alter second database, products , add new_id column

select * from second database , products table 

for each item in this table

  select from first database, products table where id =  id of second database. products table 

  if the values (same product name)  then 

   update new_id with the same id value

 else

   increment that variable by 1 , set new_id to that variable

end if

end for each

select * from second database, products where new_id > original value of that variable

for each row,

 insert values in first database.products table

 update all rows from second database, middle table change IDs to new_id values

 make some other changes to maintain relashionships

end for each

 

repeat with usernames

 

Link to post
Share on other sites
Posted · Original PosterOP
Just now, mariushm said:

Well of course not, write some script in php or some language , it's just a bunch of queries and some foreach  or while or whatever

 

If you're willing to share the databases, maybe just a small set of values, i may be willing to give it a shot in writing a script or something if i don't get bored

 

I'm not allowed to share any real data sorry :P But yea that was my idea in the start, just didn't know if there was a better way.

Link to post
Share on other sites

Export the tables from Database2 to json. loop throught it and write the data to Database1 with the new increment ID, then adjust ur other tables which has relations to it with it.


Quote or mention me if not feel ignored 

Link to post
Share on other sites
On ‎11‎/‎13‎/‎2017 at 5:17 AM, Joveice said:

Hello, so I have two databases I would like to merge.

 

Under are examples on data that will need to be merged

 

Data 1

 

| id | name  |                        | id | pr_id | user_id |             | id | product   |

| -- | ----- |                        | -- | ----- | ------- |             | -- | --------- |

| 1  | Alex  |                        | 1  | 1     | 2       |             | 1  | A product |

| 2  | Scott |                        | 2  | 3     | 1       |             | 2  | Product 2 |

| 3  | Alan  |                        | 3  | 2     | 3       |             | 3  | Something |

 

And data 2

 

 

| id | name  |                        | id | pr_id | user_id |             | id | product   |

| -- | ----- |                        | -- | ----- | ------- |             | -- | --------- |

| 2  | Jack  |                        | 2  | 3     | 2       |             | 3  | A product |

 

As you see these will conflict, is there a way to merge them? or am I best of doing this with a script?

 

at the end I would want this

 

| id | name  |                        | id | pr_id | user_id |             | id | product   |

| -- | ----- |                        | -- | ----- | ------- |             | -- | --------- |

| 1  | Alex  |                        | 1  | 1     | 2       |             | 1  | A product |

| 2  | Scott |                        | 2  | 3     | 1       |             | 2  | Product 2 |

| 3  | Alan  |                        | 3  | 2     | 3       |             | 3  | Something |

| 4  | Jack  |                        | 4  | 4     | 4       |             | 4  | A product |

create 3rd database thats blank but with rule if id conflict then id+1 on the name that caused conflict repeat till no conflict or id#max reached 

if id#max reached append database name to name#

create new database with same name but #+1

and continue data building

with these rules tell databases 1&&2 to merge/export to database3,,, or tell database3 to import databases1&&2

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now


  • Recently Browsing   0 members

    No registered users viewing this page.


×