CODEWITHSUNDEEP
phpmysqlphpmyadmin
tomb
tomb

Reputation: 1827

Export Certain Columns of All Tables in Database

I have a little problem. I have two MySQL databases, and I want to move all of the tables from one into the other, except when I use the normal PHPMyAdmin export, it comes up with an error because of my ID column. How can I copy the whole database into the other one, except without copying that one table in PHPMyAdmin? Also, I can't just copy tables manually because I have alot of tables. I know that this may involve looping over all of the tables in a database. Anybody know of a way to do that (I am OK if it uses PHP)?
All of the tables are exactly the same in structure, and follow this structure:

id int NOT NULL AUTO_INCREMENT, # The one I don't want to copy (it is also a primary key)
col1 varchar(5), # Want to copy all of these.
col2 varchar(15),
col3 varchar(1000),
col4 varchar(20)

All I am wanting to do is copy all of the tables from one database to another database, except without copying the id column of each entry. So, for example, if I was just to add one entry manually I would use CREATE TABLE IF NOT EXISTS mytable ..., and then INSERT INTO mytable (col1, col2, col3, col4) VALUES (value1, value2, value3, value4). Note that I am not inserting the id column.

Upvotes: 0

Views: 1207

Answers (7)

Ragen Dazs
Ragen Dazs

Reputation: 2170

This is my solution - PHP needs be configured in sudoers

<?php

// Dump and recover 

system("mysqldump -u root -ppassword -h localhost --ignore-table=my_db_name.my_table_name1 --ignore-table=my_db_name.my_table_name2 my_db_name | mysql -u root -ppassword -h remote_host my_db_name");

If you not needs as PHP you can run just this command line in the shell

mysqldump -u root -ppassword -h localhost --ignore-table=my_db_name.my_table_name1 --ignore-table=my_db_name.my_table_name2 my_db_name | mysql -u root -ppassword -h remote_host my_db_name

Upvotes: 0

SubRed
SubRed

Reputation: 3187

You can crete PHP script to get all of your tables and do looping to insert into your new tables. If the structure is the same then it should be easier to copy all of them. Here the ideas:

Get all tables

$sql = "SHOW TABLES FROM DB1";
$query = mysql_query($sql);    

// save the result as $old_tables

$sql = "SHOW TABLES FROM DB2";
$query = mysql_query($sql);    

// save the result as $new_tables

Loop the result and insert to your other tables, create if it does not exist:

foreach($old_tables as $table){
    if(!in_array($table, $new_tables)){
        // create table with the name $table in DB2
    }

    $sql = "INSERT INTO DB2.$table(col1, col2, col3, col4) SELECT col1, col2, col3, col4 FROM  DB1.$table";
    mysql_query($sql);
}

The easiest way is using DB Admin Tool like SQLYog, Navicat, etc.

Upvotes: 1

Blake B.
Blake B.

Reputation: 295

I've used "Navicat for MySQL" to copy all sorts of data between databases. It sounds like it might work well for your use case. Navicat is quite flexible when it comes to cross-database data management. You can download a 30-day trial, experiment with it and decide if it works for you. http://www.navicat.com

Note: I do not work for Navicat...I'm just a satisfied customer.

Upvotes: 0

Diego Pino
Diego Pino

Reputation: 11586

OK, this is the crazy approach.

1) Do a dump of your MySQL database

mysqldump -h localhost -u <user> -p<password> <database> > dump.sql

2) Open dump.sql with Vim and add (col1, col2, col3, col4) after the name of all tables

vim dump.sql
:%s/INSERT INTO `\(.*\)`/INSERT INTO `\1`(col1, col2, col3, col4)/g

3) Now remove the first value after VALUES, which should be the id for each row.

:%s/VALUES (\d\+,/VALUES (/g

4) Remove all tables you don't want to copy from the dump. Apply the dump to your destination database.

Upvotes: 0

Stephen O&#39;Flynn
Stephen O&#39;Flynn

Reputation: 2329

This guy here has a bash script that can get table names from a database and perform an action on each one: http://wozia.com/blog/tips/bash-script-to-repair-all-mysql-tables-for-all-databases/

If you were to combine that with @Lex's answer above, it might do what you are looking for. Specifically, use the second file only on the linked page, and change the mysql line to something like:

INSERT INTO targetdb.$TABLENAME (col1, col2, col3 col4)
    VALUES (SELECT col1, col2, col3, col4
            FROM sourcedb.$TABLENAME)

I'm sure you'd have to work it a little to have it reference the correct columns. You'd also be stuck with no duplicate or error checking, but I assume you've already considered that.

Upvotes: 0

exussum
exussum

Reputation: 18550

select concat( 'INSERT INTO destination_db.destintation_table 
(col1, col2, col3, col4)
SELECT col1, col2, col3, col4 FROM ',a.table_name,';' )
from information_schema.tables a 
where a.table_name like 'table_prefix_%';

should give you code to run assuming your tables all have a prefix ? After its come back run the query it returns

Upvotes: 1

user254875486
user254875486

Reputation: 11240

If you have a user that has access to both databases, you can do something like this:

INSERT INTO destination_db.destintation_table 
    (col1, col2, col3, col4)
    SELECT col1, col2, col3, col4 FROM source_db.source_table

Upvotes: 0

Related Questions