CODEWITHSUNDEEP
mysqlsql
B.Mr.W.
B.Mr.W.

Reputation: 19628

MySQL append/insert from a different server

I have a table on the development box with exactly the same format as another one on the production server. The data on the development need to be appended/inserted into the production where I don't have the permission to create a table.

I was thinking about doing something like insert into production_table select * from develop_table, however, since I cannot create a new table develop_table, then this is impossible to do.

I am using Sequal Pro, and I don't know is there a way to export my development table to a file (CSV/SQL), then I can run some command on my client side to load from that file into the production without overwriting the production table?

Upvotes: 0

Views: 117

Answers (1)

Barranka
Barranka

Reputation: 21047

Assuming your production table has primray / unique key(s), you can export the data in your development server as a .csv file, and load it into your production server with load data, specifying if you want to replace/ignore the duplicated rows.

Example:

In your development server you must export the data to a .csv file. You can use select into... to do that:

select *
    into outfile '/home/user_dev/your_table.csv'
    fields terminated by ',' optionally enclosed by '"'
    lines terminated by '\n'
from your_table;

In your production server, copy the your_table.csv file and load it using load data...:

load data infile '/home/user_prod/your_table.csv'
replace -- This will replace any rows with duplicated primary | unique key values.
        -- If you don't want to replace the rows, use "ignore" instead of "replace"
into table your_table
fields terminated by ',' optionally enclosed by '"'
lines terminated by '\n';

Read the reference manual (links provided above) for additional information.

Upvotes: 2

Related Questions