Migrate rows for import into existing Database, taking care of changing ids etc

Question

The purpose of this is to copy some rows from one environment to another without overwriting existing rows.

Sample DB:

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

The idea is school_id and class_id are auto-increments and class has a Foreign Key link back to school. But I want to dump just these rows and insert them into another database that already has a school_id of 15.

It might be something that could look like:

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

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

What would be the proper tool to do this kind of operation? Can mysqldump do anything this smart?

Answer 1

You can do this:

• Find MAX school_id in the target school table -

  SELECT MAX(school_id) INTO @max_school_id FROM school;

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

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

It might be very usefull to add ON UPDATE CASCADE action to the foreign key, it will help to change school_id in the child table automatically, e.g. -

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;

• Make 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, increment id values: We should update all unique values, in our case we have to update class_id in the class table and school_id in the school table.

Find max class_id for the TARGET class table

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

Increment all SOURCE class_id values class_id + 1012

UPDATE class SET class_id = class_id + 1012;

Find max school_id for the TARGET school table

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

Increment all SOURCE school_id values school_id + 1015

UPDATE school SET school_id = school_id + 1015;

That is all. We can dump 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 against the target database.

Answer 2

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

Answer 3

If you don't need a pure SQL solution you can very easily create a script that reads from the old database and writes to the new. I'm thinking 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 table privileges you could 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 right but it needs a bit of checking.

Answer 5

One really simple trick would be to just multiply the id's with -1. Negative id is as good as any id, and I assume that your auto_increment columns start with positive numbers anyways.

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 into second:

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

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

Generic solution would be to use write the migration logic yourself, either in ETL tool or just as a standalone script, as others have stated. And make that tool/script to use INFORMATION_SCHEMA.TABLES and INFORMATION_SCHEMA.COLUMNS to dynamically find out the tables and columns that need to be adjusted.

Answer 6

I think the best way is to eliminate the ids from the transfer. assuming that the school names are unique, this seemed to work on deviant's schema, and for simplicity, having the two databases on the same server:

Copy the data into a table on the new database with no ids, just 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 the new schools to the school table:

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

Add the new classes (for both existing and new schools) to 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 there are classes in the target databases for schools in the source that don't have that class? This is a union, if you want delete you will have to change it.

Answer 7

if you are using this command in php, then there is a simple function which will give you the last id of your insert query. i.e. mysql_insert_id().

code may 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 are using in some other language I don't know what has to be done.