CODEWITHSUNDEEP
mysqldatabaseimportexport
pablofiumara
pablofiumara

Reputation: 1797

MySQL - Merge data from two tables (they are on different servers)

Suppose you have table A (database named test) on server 1 and you have table A (database named test) on server 2.

Table A has rows like this

(1, 'first row table A')

(2, 'second row table A')

Note: First column do not use auto increment

Table B has rows like this

(1, 'first row table B')

(2, 'second row table B')

I would like to get on a third server a table named A (and database named test) with the following rows

(1, 'first row table A')

(2, 'second row table A')

(3, 'first row table B')

(4, 'second row table B')

I tried using mysqldump but first column is causing issue 'duplicate key' when trying to import the second dump. I am using MySQL. This should be done for multiple tables. What should I do? Thanks in advance

Upvotes: 0

Views: 512

Answers (1)

Barmar
Barmar

Reputation: 781716

Load the dump into a table with a different name. See Mysqldump: Can you change the name of the table you're inserting into? for how you can rename the table in the dump file.

Then merge the data from the temp table, adjusting the first column so they're outside the range from the original table.

INSERT INTO TableA (col1, col2)
SELECT col1 + increment, col2 FROM temp_TableA
CROSS JOIN (SELECT MAX(col1) AS increment FROM TableA) AS t

Upvotes: 1

Related Questions