Splitting a column

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.

Scripts

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

Source table script example	Target table script example
CREATE TABLE [dbo].[Person]( [PersonID] [INT] IDENTITY PRIMARY KEY, [PersonNumber] [INT] NOT NULL, [PersonFirstName] [NVARCHAR](25) NULL, [PersonLastName] [NVARCHAR](25) NULL ) GO	CREATE TABLE [dbo].[Person]( [PersonID] [INT] IDENTITY PRIMARY KEY, [PersonNumber] [INT] NOT NULL, [PersonName] [NVARCHAR](50) NULL ) GO INSERT INTO [dbo].[Person]([PersonNumber], [PersonName]) VALUES (74, 'Alan Dou'); INSERT INTO [dbo].[Person]([PersonNumber], [PersonName]) VALUES (75, 'Jordan Sanders'); GO

Source table script example

Target table script example

CREATE TABLE [dbo].[Person](
[PersonID] [INT] IDENTITY PRIMARY KEY,
[PersonNumber] [INT] NOT NULL,
[PersonFirstName] [NVARCHAR](25) NULL,
[PersonLastName] [NVARCHAR](25) NULL
)
GO

CREATE TABLE [dbo].[Person](
[PersonID] [INT] IDENTITY PRIMARY KEY,
[PersonNumber] [INT] NOT NULL,
[PersonName] [NVARCHAR](50) NULL
)
GO
 
INSERT INTO [dbo].[Person]([PersonNumber], [PersonName])
    VALUES (74, 'Alan Dou');
INSERT INTO [dbo].[Person]([PersonNumber], [PersonName])
    VALUES (75, 'Jordan Sanders');
GO

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 [dbo].[Person] DROP COLUMN [PersonName] GO ALTER TABLE [dbo].[Person] ADD [PersonFirstName] [NVARCHAR](25) NULL GO ALTER TABLE [dbo].[Person] ADD [PersonLastName] [NVARCHAR](25) NULL GO	ALTER TABLE [dbo].[Person] ADD [PersonFirstName] [NVARCHAR](25) GO ALTER TABLE [dbo].[Person] ADD [PersonLastName] [NVARCHAR](25) GO -- migration script: UPDATE [dbo].[Person] SET [PersonFirstName] = LEFT([PersonName],CHARINDEX(CHAR(32), [PersonName]) - 1) GO UPDATE [dbo].[Person] SET [PersonLastName] = RIGHT([PersonName],LEN([PersonName]) - CHARINDEX(CHAR(32),[PersonName])) GO -- migration script. ALTER TABLE [dbo].[Person] DROP COLUMN [PersonName]; GO

Script generated by the Schema Comparison engine

Script that should be generated manually for errorless data migration

ALTER TABLE [dbo].[Person]
    DROP COLUMN [PersonName]
GO
 
ALTER TABLE [dbo].[Person]
    ADD [PersonFirstName] [NVARCHAR](25) NULL
GO
 
ALTER TABLE [dbo].[Person]
    ADD [PersonLastName] [NVARCHAR](25) NULL
GO

ALTER TABLE [dbo].[Person]
ADD [PersonFirstName] [NVARCHAR](25)
GO
ALTER TABLE [dbo].[Person]
ADD [PersonLastName] [NVARCHAR](25)
GO
 
-- migration script:
 
UPDATE [dbo].[Person]
SET [PersonFirstName] =
LEFT([PersonName],CHARINDEX(CHAR(32),
[PersonName]) - 1)
GO
 
UPDATE [dbo].[Person]
SET [PersonLastName] =
RIGHT([PersonName],LEN([PersonName]) -
CHARINDEX(CHAR(32),[PersonName]))
GO
 
-- migration script.
 
ALTER TABLE [dbo].[Person]
DROP COLUMN [PersonName];
GO

Want to Find out More?

Download a free 30-day trial

Splitting a column

Want to Find out More?

Overview

All features

Request a demo