Splitting a table

Last modified: March 28, 2025

If you split a table into two tables in a database schema, it involves creating a new table and removing columns from the original table. However, the deployment will lead to data loss of the dropped table.

Solution: To prevent data loss, you need to create a migration script to copy the data from the original table to the new table before you delete columns from the original table. The process involves the following steps:

  • Create a new table
  • Generate a migration script to copy data to the new table and execute it
  • Drop the column from the original table

Note

The comparison engine of the tools doesn’t handle all cases automatically, which is why the cases mentioned above must be resolved manually.

The screenshot displays the Schema Comparison document and the scripts generated by Schema Compare Engine and a migration script, which should be created manually to perform errorless data migration.

Scripts

The table provides source and target table script examples to illustrate the case:

Source table script example Target table script example 
CREATE TABLE Person (
  PersonNumber INT NOT NULL,
  PersonName VARCHAR(50) NULL,
  PRIMARY KEY CLUSTERED (PersonNumber)
);

CREATE TABLE PersonAddress (
  AddressID INT AUTO_INCREMENT PRIMARY KEY,
  PersonNumber INT NOT NULL,
  AddressLine VARCHAR(50) NULL,
  State VARCHAR(50) NULL,
  Zip VARCHAR(50) NULL
);

ALTER TABLE PersonAddress
  ADD FOREIGN KEY (PersonNumber)
  REFERENCES Person (PersonNumber);

CREATE TABLE Person (
  PersonNumber INT PRIMARY KEY,
  PersonName VARCHAR(50) NULL,
  PersonAddress VARCHAR(150) NULL
);

INSERT INTO Person (PersonNumber, PersonName, PersonAddress)
  VALUES (74, 'Alan Dou', '2590 Main St. Bernalillo, Albuquerque, New Mexico, NM, 87102');
INSERT INTO Person (PersonNumber, PersonName, PersonAddress)
  VALUES (75, 'Jordan Sanders', '1234 Broadway Street, Apt. 77, New York, NY, 10101');

The table provides examples of scripts generated by the Schema Comparison engine and those that should be generated manually for errorless data migration to illustrate the case:

Script generated by the Schema Comparison engine Script that should be generated manually for errorless data migration 
ALTER TABLE person
  DROP COLUMN PersonAddress;

CREATE TABLE personaddress (
  AddressID INT NOT NULL AUTO_INCREMENT,
  PersonNumber INT NOT NULL,
  AddressLine VARCHAR(50) DEFAULT NULL,
  State VARCHAR(50) DEFAULT NULL,
  Zip VARCHAR(50) DEFAULT NULL,
  PRIMARY KEY (AddressID)
)
ENGINE = INNODB,
CHARACTER SET latin1,
COLLATE latin1_swedish_ci;

ALTER TABLE personaddress
  ADD CONSTRAINT personaddress_ibfk_1 FOREIGN KEY (PersonNumber)
  REFERENCES person(PersonNumber);

CREATE TABLE PersonAddress (
  AddressID INT AUTO_INCREMENT PRIMARY KEY,
  PersonNumber INT NOT NULL,
  AddressLine VARCHAR(50) NULL,
  State VARCHAR(50) NULL,
  Zip VARCHAR(50) NULL,
  FOREIGN KEY (PersonNumber) REFERENCES Person (PersonNumber)
);

-- migration script.
INSERT INTO PersonAddress (PersonNumber, AddressLine, State, Zip)
SELECT
  PersonNumber,
  TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(PersonAddress, ',', 1), ',', -1)) AS AddressLine,
  TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(PersonAddress, ',', 2), ',', -1)) AS State,
  TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(PersonAddress, ',', -1), ',', 1)) AS Zip
FROM Person;

ALTER TABLE Person
  DROP COLUMN PersonAddress;

Want to find out more?

Overview

Overview

Take a quick tour to learn all about the key benefits delivered by Schema Compare for MySQL.

Request a demo

Request a demo

If you consider employing this tool for your business, request a demo to see it in action.

Schema Compare for MySQL logo

Ready to start using  

Schema Compare for MySQL?

Download a FREE 30-day trial