Copy Subset of Rows in Database Tables from One Database to Another

Question

I have a productions Database with exact same schema as test database. The schema consists of more than 100 tables and its a typical relational database schema meaning that Foreign Keys are extensively used. What I need to achieve is to copy a subset of data e.g. All employees with employee ID > 100 from one database to another. Along with populating Employee table I also want that all the other tables referring to this table should also get populated. E.g. All personal detail of Employee ID 101 should get populated in Employee Table but also the check IN and Checkout record of employee 101 should be copied in LogInOut table. Task table should also get record all of task tasked assigned to Employee 101. Please help!

Answer 1

Use SELECT INTO OUTFILE to export the limited dataset in a CSV file.

Something like:

SELECT *
FROM employees
WHERE ID > 100
INTO OUTFILE '/tmp/employees.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Do the same for the other tables.

Then import them to your dev database using LOAD DATA INFILE.