Splitting a column

Last modified: March 28, 2025

When splitting a column in a database schema, it involves creating two new columns and removing the original column. However, data loss can occur during the script deployment.

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

• Create two new columns
• Generate a migration script to copy data to new columns
• Delete the original column

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.

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

CREATE TABLE person (
  PersonID INT AUTO_INCREMENT PRIMARY KEY,
  PersonNumber INT NOT NULL,
  PersonFirstName VARCHAR(25) NULL,
  PersonLastName VARCHAR(25) NULL
);

CREATE TABLE person (
  PersonID INT AUTO_INCREMENT PRIMARY KEY,
  PersonNumber INT NOT NULL,
  PersonName VARCHAR(50) NULL
);

INSERT INTO person (PersonNumber, PersonName)
  VALUES (74, 'Alan Dou');
INSERT INTO Person (PersonNumber, PersonName)
  VALUES (75, 'Jordan Sanders');

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:

ALTER TABLE person
  DROP COLUMN PersonName;

ALTER TABLE person
  ADD COLUMN PersonFirstName VARCHAR(25) DEFAULT NULL;

ALTER TABLE person
  ADD COLUMN PersonLastName VARCHAR(25) DEFAULT NULL;

ALTER TABLE person
  ADD COLUMN PersonFirstName VARCHAR(25) DEFAULT NULL;

ALTER TABLE person
  ADD COLUMN PersonLastName VARCHAR(25) DEFAULT NULL;

-- migration script:

UPDATE person
SET PersonFirstName = SUBSTRING_INDEX(PersonName,' ',1),
PersonLastName = SUBSTRING_INDEX(PersonName,' ',-1);

-- migration script.

ALTER TABLE person
  DROP COLUMN PersonName;