Copy selected records from one MySQL host/server to another.

As I know copying tables from database on one server to database on another server whould in each case mean exporting/importing. What you need to do is to select needed data range (just as in example that you provided, using SELECT query). Then you need to export it to .sql file. If you are using phpMyAdmin tool, you should have 'export' tab in phpMyAdmin menu or 'export' button in the bottom of the page when browsing tables. Use it to create .sql file.

In any case, you will get .sql file containing queries:

CREATE TABLE IF NOT EXISTS `table` ();

and

INSERT INTO 'table' VALUES ();

After that you will either need to import .sql file using 'import' button in phpMyAdmin on your new database on another server. Or simply by executing queries from your new file.

Check the manual for the SELECT ... INTO OUTFILE syntax, as it may help you.
http://dev.mysql.com/doc/refman/5.1/en/select.html

You can additionally use the INFORMATION_SCHEMA to help you.
For example:

SELECT table_name
FROM information_schema.columns
WHERE column_name = 'projectid'
;

Select allOpen in new window

With this, you can use prepared statements to run dynamic SQL you generate from the results of the INFORMATION_SCHEMA query above. Manual page for prepared statements: http://dev.mysql.com/doc/refman/5.1/en/sql-syntax-prepared-statements.html
Example solution I just provided: https://www.experts-exchange.com/questions/27397709/Need-help-creating-a-MySQL-function-or-stored-procedure-to-run-the-following-working-query-with-variables.html?anchorAnswerId=36972117#a36972117

In your case, I would have the SQL generated with the table_name as file name. Then you can use INFORMATION_SCHEMA on the other side to generate matching LOAD DATA INFILE statements. Thought there is predicated on the table names matching, so you can take advantage of finding file names dynamically. If not, then you will just have to manually execute your LOAD DATA INFILE statements associating the proper table to each file.
http://dev.mysql.com/doc/refman/5.1/en/load-data.html

Hope that helps!

Trackstudio10: I don't have phpmyadmin. I'm using mysql administrator 1.2.17 and MySQL Query Browser.

mwvisa1:
Can you explain a little more about information_schema.columns in your example?

I looked it up and does information_schema show all tables ?
or does the ".columns" show all the columns for each table?

So does the one SELECT statement you provided above output for all table without the need to loop thru and name each?

So the following would do what I need:

SELECT table_name INTO OUTFILE = table_name.txt
FROM information_schema.columns
WHERE column_name = 'projectid'
;

INFORMATION_SCHEMA.COLUMNS contains columns for each table. INFORMATION_SCHEMA.TABLES would show all the tables. Since you want to only grab tables that have projectid since it is required in the filter, I used the COLUMNS view. You do NOT want to use OUTFILE on that query unless of course all you want is a list of tables. You would instead create dynamic SQL using that table and then used prepared statements to execute. That is what the linked EE questions shows you how to do.

An example would be:

SELECT
GROUP_CONCAT(
   CONCAT('SELECT * 
    FROM `', table_name, '`
    WHERE `', column_name, '` = 123
    INTO OUTFILE ''c:/temp/', table_name, '.csv''
    FIELDS TERMINATED BY '','' OPTIONALLY ENCLOSED BY ''"''
    LINES TERMINATED BY ''\\n'';')
   SEPARATOR '\n'
)
FROM information_schema.columns
WHERE column_name = 'projectid'
INTO @sqlstring
;

-- you can open value in viewer, then execute scripts
SELECT @sqlstring;

Select allOpen in new window

As suggested in the comment, you can grab the output SQL string which will have each command separated by a semi-colon and a new line, e.g.:

SELECT * 
    FROM `example table 1`
    WHERE `projectid` = 123
    INTO OUTFILE 'c:/temp/example table 1.csv'
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    LINES TERMINATED BY '\n';
SELECT * 
    FROM `example table 2`
    WHERE `projectid` = 123
    INTO OUTFILE 'c:/temp/example table 2.csv'
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    LINES TERMINATED BY '\n';

Select allOpen in new window

Note: if the file already exists, then it will throw and error.

Additionally, to move from the example to final code, you may need to use a WHILE loop grabbing LIMIT 1 each time as prepared statements can only do one sql statement at a time as stated in documentation, which gives me a better idea on how to structure this. I will post back, but hopefully that helps for now.

Kevin

Please elaborate more on steps 17.

Thanks

You mean "you can open value in viewer, then execute scripts?" For that step, I am sorry I am using MySQL Workbench, so keep thinking in those terms. When results come back in MySQL Workbench, you can right-click on the contents in a cell and use "Open Value in Viewer" -- I cannot recall if MySQL Query Browser had that. I remember getting the results back and typically saving results as. If you can get the results out to a .sql file that you then run through MySQL Query Browser then great. The point of that step is SELECT @sqlstring brings back the content compiled by the INFORMATION_SCHEMA query and so is a long set of multiple SQL statements that you need to copy and paste into a script tab and execute.

Actually with that in mind, you can even forget about the GROUP_CONCAT().

SELECT
CONCAT('SELECT * 
    FROM `', table_name, '`
    WHERE `', column_name, '` = 123
    INTO OUTFILE ''c:/temp/', table_name, '.csv''
    FIELDS TERMINATED BY '','' OPTIONALLY ENCLOSED BY ''"''
    LINES TERMINATED BY ''\\n'';') AS OutfileScripts
FROM information_schema.columns
WHERE column_name = 'projectid'
;

Select allOpen in new window

Run this, and you will get a row with OUTFILE script per table name.

I  tried the SELECT above and it didn't return anything.
Could it be the "WHERE" clause?

The column "projectid" is a text field so does 123 need to be in quotes?
I tried adding single quotes but got an error.

Still not returning anything. I even tried different values, columns and tables.

Could it be I have to specify the database name somewhere?

Any ideas?

What happens when you just run SELECT * FROM INFORMATION_SCHEMA.COLUMNS?

Sorry, got smashed with another emergency project. Will try your suggestion above.

Thanks

Wonderful. Just post back when you have results and want to continue (if still necessary).