Re: Copy Subset of Table to another Table

[Scout Thompson]

Yes, there is a way around this — you can export only a subset of data (filtered by a WHERE clause) from a MySQL table and import it as actual data (not a view) into the destination database.

Your idea to use a VIEW was close, but as you noticed, it just recreates the view on the target database, not a real table with data.
✅ Recommended Solution: Use --where with mysqldump

You can tell mysqldump to export only a subset of the data using the --where option:

"\\mysqlPath\mysqldump.exe" -h 10.1.1.1 -uroot -ppassword dbname customers --where="countryID = 'DE'" > \\sqlPath\customers.sql
"\\mysqlPath\bin\mysql.exe" -uroot -ppassword -h localhost dbname2 < \\sqlPath\customers.sql

This will:

Dump only the rows from the customers table where countryID = 'DE'

Generate a .sql file that includes table structure and those filtered rows

Import that into dbname2, creating or replacing the customers table