0

I have two SQL databases in phpMyadmin, db1 and db2. Say db1 has 3 tables: 1_a, 1_b, 1_c; and db2 has 3 tables: 2_d, 2_e, 2_f. I would like to transfer my db1's 1_a,1_b,1_c over to db2 and replace with db2's 2_d,2_e,2_f.

So, firstly, I will have to select 1_a, 1_b, 1_c tables from db1, and export them as a SQL file. Afterwards, I will have to select 2_d,2_e,2_f tables from db2, and drop them from db2. Thirdly, I just have import the file from first step to db2.

I am wondering what are the correct SQL statements for first and second steps? Assuming all tables that I am going to move around have their prefix like 1_x and 2_x, and there are some other tables in db1 and db2 that has no prefix and I do not want to touch them. Appreciate your answers.

Terry Chen
  • 427
  • 6
  • 9

1 Answers1

1

you can use mysqldump to backup 3 tables or use select ... into outfile syntax for csv output:

SELECT * FROM `1_a` INTO '/home/1_a.txt'
SELECT * FROM `1_b` INTO '/home/1_b.txt'
SELECT * FROM `1_c` INTO '/home/1_c.txt'

or better yet use phpmyadmin backup function. and afterwards you should use 

DROP TABLE `2_d`;
DROP TABLE `2_e`;
DROP TABLE `2_f`;

to drop tables in second database

Volkan Ulukut
  • 4,230
  • 1
  • 20
  • 38
  • is there a way I can select all tables that has prefix 1_x and 2_x? As if I have thousands of tables needed to be operated. – Terry Chen Feb 06 '14 at 15:25
  • to drop tables with prefixes check this out: http://stackoverflow.com/questions/1589278/sql-deleting-tables-with-prefix – Volkan Ulukut Feb 06 '14 at 15:31
  • to dump tables with prefixes check this out: http://stackoverflow.com/questions/5268936/mysqldump-only-tables-with-certain-prefix-mysqldump-wildcards – Volkan Ulukut Feb 06 '14 at 15:32