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.