Migrating rows to import into an existing database, taking care of changing identifiers, etc.

Question

The goal of this is to copy some lines from one environment to another without overwriting existing lines.

DB example:

INSERT INTO `school` (school_id,name) VALUES (15,'Middle'); INSERT INTO `class` (class_id,school_id,name) VALUES (12,15,'Sample'); 

The idea of school_id and class_id are automatic increments, and the class has a foreign key link back to school . But I want to dump only these rows and paste them into another database that already has school_id of 15.

Perhaps this might look like this:

 INSERT INTO `school` (name) VALUES ('Middle'); INSERT INTO `class` (school_id,name) VALUES (LAST_INSERT_ID(),'Sample'); 

But that would be simple for this simple example. Imagine if I had 50 classes, 25 students in each and several hundred classes for each combination of students / classes. You could see how LAST_INSERT_ID() might not work without storing it in a series of variables.

What would be a suitable tool for this kind of operation? Can mysqldump do anything so smart?

Answer 1

You can do it:

• Find MAX school_id in the school target table -

  SELECT MAX (school_id) INTO @max_school_id from school;

• Change all school_id values ​​in the source tables ( school , class ) - add MAX school_id from the previous point -

  UPDATE school SET school_id = school_id + @max_school_id + 1;

It can be very useful to add the action "ON UPDATE CASCADE" to the foreign key, this will help to automatically change school_id in the child table, for example. -

 ALTER TABLE class DROP FOREIGN KEY FK_name; ALTER TABLE class ADD CONSTRAINT FK_name FOREIGN KEY (school_id) REFERENCES school(school_id) ON UPDATE CASCADE; 

• Dump and import.

---

Explanation and example:

Create source tables:

 CREATE TABLE school( school_id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20) ); INSERT INTO school (school_id, name) VALUES (1, 'Middle1'), (2, 'Middle2'), (3, 'Middle3'), (15, 'Middle'); CREATE TABLE class( class_id INT(11) NOT NULL, school_id INT(11) DEFAULT NULL, name VARCHAR(20) DEFAULT NULL, PRIMARY KEY (class_id), CONSTRAINT FK_class_school_school_id FOREIGN KEY (school_id) REFERENCES school (school_id) ON DELETE RESTRICT ON UPDATE CASCADE ) ENGINE = INNODB; INSERT INTO class (class_id, school_id, name) VALUES (11, 1, 'Sample1'); INSERT INTO class (class_id, school_id, name) VALUES (12, 15, 'Sample'); 

Create target tables:

 CREATE TABLE school( school_id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20) ); INSERT INTO school (school_id, name) VALUES (1, 'Top'), (2, 'Middle'), (3, 'Bottom'), (15, 'Top'); CREATE TABLE class( class_id INT(11) NOT NULL, school_id INT(11) DEFAULT NULL, name VARCHAR(20) DEFAULT NULL, PRIMARY KEY (class_id), CONSTRAINT FK_class_school_school_id FOREIGN KEY (school_id) REFERENCES school (school_id) ON DELETE RESTRICT ON UPDATE CASCADE ) ENGINE = INNODB; INSERT INTO class (class_id, school_id, name) VALUES (10, 2, 'Sample2'); INSERT INTO class (class_id, school_id, name) VALUES (12, 15, 'Sample'); 

Update source tables, increase id values: We must update all unique values, in our case we need to update class_id in the class table and school_id in the school table.

Find max class_id for table TARGET class

 SELECT MAX(class_id) + 1000 FROM class; -- This will return => 1012 

Increase all values. SOURCE class_id class_id + 1012

 UPDATE class SET class_id = class_id + 1012; 

Find max school_id for table TARGET school

 SELECT max(school_id) + 1000 FROM school; -- This will return =>1015 

Increase all values ​​of SOURCE school_id school_id + 1015

 UPDATE school SET school_id = school_id + 1015; 

That's all. We can reset the source tables:

 INSERT INTO school VALUES (1016, 'Middle1'), (1017, 'Middle2'), (1018, 'Middle3'), (1030, 'Middle'); INSERT INTO class VALUES (1023, 1016, 'Sample1'), (1024, 1030, 'Sample'); 

Now we can easily run this script in relation to the target database.

Answer 2

Do you need to do this in SQL? Even the most basic ETL tools are better suited. Try pentaho or talend instead.

Answer 3

If you don't need a clean SQL solution, you can easily create a script that reads from the old database and writes to the new one. I think PHP, Python, Ruby, Perl, ...

Here is a simple solution in PHP, assuming you are using mysql and moving data between databases ( untested, may contain errors ):

 $dbh1 = new PDO("mysql:host=db1.host;dbname=db1", "user1", "pass1"); $dbh2 = new PDO("mysql:host=db2.host;dbname=db2", "user2", "pass2"); $sth1 = $dbh1->query(" SELECT school.school_id as school_id, school.name as school_name, class.name as class_name FROM school JOIN class ON (school.school_id = class.school_id) "); $sth3 = $dbh2->prepare("INSERT INTO school (name) VALUES (:name)"); $sth4 = $dbh2->prepare("INSERT INTO class (school_id, name) VALUES (:school_id, :name)"); $schools = array(); // get schools and classes while ($school = $sth1->fetch(PDO::FETCH_ASSOC)) { $school_id = $school['school_id']; $school_name = $school['school_name']; $schools[$school_id]['school_name'] = $school_name; $schools[$school_id]['classes'][] = array( 'class_name' => $school['class_name'] ); } // insert schools and classes foreach ($schools as $school_id => $school) { // insert school $sth3->bindParam(':name', $school['school_name'], PDO::PARAM_INT); $sth3->execute(); $new_school_id = $dbh2->lastInsertId(); // a loop for classes foreach ($school['classes'] as $class) { // insert class $sth4->bindParam(':school_id', $new_school_id, PDO::PARAM_INT); $sth4->bindParam(':name', $class['class_name'], PDO::PARAM_STR); $sth4->execute(); } // a loop for another joined table /* foreach ($school['joined'] as $join) { // insert join $sth4->bindParam(':school_id', $new_school_id, PDO::PARAM_INT); $sth4->bindParam(':name', $join['join_name'], PDO::PARAM_STR); $sth4->execute(); } */ } 

Answer 4

If you have temporary privileges for tables, you can do:

 CREATE TEMPORARY TABLE tmp_school LIKE school; LOAD DATA LOCAL INFILE 'school.dat' INTO TABLE tmp_school; CREATE TEMPORARY TABLE tmp_class LIKE class; LOAD DATA LOCAL INFILE 'class.dat' INTO TABLE tmp_class; INSERT INTO school (name) SELECT name FROM tmp_school; INSERT INTO class (school_id,name) SELECT school.school_id, class.name FROM school school JOIN tmp_school new USING(name) JOIN tmp_class class ON new.school_id = class.school_id 

I think this is correct, but a little check is needed.

Answer 5

One simple trick is to simply multiply id by -1. A negative id is just as good as any id, and I assume that your auto_increment columns start with positive numbers anyway.

Export from one environment:

 select -1*school_id, name from school into outfile 'school.out'; select -1*class_id, -1*school_id, name from class into outfile 'class.out'; 

Import in the second:

 load data infile 'school.out' into table school; load data infile 'class.out' into table class; 

Obviously, this is not a general solution to your problem, but how often do you need it? :)

A universal solution would be to use the migration logic yourself, either in the ETL tool or as a stand-alone script, as others have stated. And make this tool / script to use INFORMATION_SCHEMA.TABLES and INFORMATION_SCHEMA.COLUMNS to dynamically find the tables and columns that need to be adjusted.

Answer 6

I think the best way is to remove the identifiers from the transfer. Assuming the school names are unique, this seems to work on a deviant schema and for simplicity, having two databases on the same server:

Copy the data into a table in a new database without identifiers, just the names:

 CREATE TEMPORARY TABLE tmp AS SELECT s.name as school_name, c.name as class_name FROM test.school s JOIN test.class c USING(school_id); 

Add new schools to the school desk:

 INSERT INTO school (name) SELECT DISTINCT school_name FROM tmp LEFT JOIN school ON school_name = name WHERE name IS NULL; 

Add new classes (for both existing and new schools) in the class table:

 INSERT INTO class (name, school_id) SELECT class_name, school_id FROM tmp t JOIN school s ON s.name = t.school_name; 

What semantics do you want if the target databases have classes in the sources that don't have this class? This union, if you want to delete, you have to change it.

Answer 7

if you use this command in php then there is a simple function that will give you the last id of your insert request. those. mysql_insert_id ().

The code could be like this:

 <?php $query = mysql_query("INSERT INTO `school` (school_id,name) VALUES (15,'Middle')"); $last_id = mysql_insert_id(); INSERT INTO `class` (class_id,school_id,name) VALUES ('$last_id','Sample'); ?> 

If you use any other language, I do not know what to do.