Merge two SQL Databases and keep "pivot" id

[Joveice]

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 |

[ImNotThere]

what are the primary keys? 

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

[mariushm]

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.

 

 

 

[Joveice]

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?

[ImNotThere]

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?

[mariushm]

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

 

[Joveice]

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  But yea that was my idea in the start, just didn't know if there was a better way.

[Cruorzy]

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.

[bcguru9384]

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