Merging columns

When you merge a column in a database schema, it is necessary to create a new column and delete two original columns. However, data loss can occur when deploying the script.

Solution: To prevent data loss, it is necessary to create a migration script to copy data to new columns and execute it before dropping the original column. The process involves the following steps:

  • Create a new column
  • Create a migration script to transfer data to the new column and execute it
  • Drop original columns

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,
[PersonNumber] [INT] NOT NULL,
[PersonName] [NVARCHAR](50) NULL,
PRIMARY KEY CLUSTERED ([PersonID])
)
GO

CREATE TABLE [dbo].[Person] (
[PersonID] [INT] IDENTITY,
[PersonNumber] [INT] NOT NULL,
[PersonFirstName] [NVARCHAR](25) NULL,
[PersonLastName] [NVARCHAR](25) NULL,
PRIMARY KEY CLUSTERED ([PersonID])
)
GO
INSERT [dbo].[Person] ([PersonNumber], [PersonFirstName], [PersonLastName])
    VALUES (71, N'Alan', N'Dou')
INSERT [dbo].[Person] ([PersonNumber], [PersonFirstName], [PersonLastName])
    VALUES (72, N'Jordan', N'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 [PersonFirstName]
GO
ALTER TABLE [dbo].[Person]
    DROP COLUMN [PersonLastName]
GO
ALTER TABLE [dbo].[Person]
    ADD [PersonName] [NVARCHAR](50) NULL
GO

ALTER TABLE [dbo].[Person]
ADD [PersonName] [NVARCHAR](50) NULL
GO
 
-- migration script:
 
UPDATE [dbo].[Person]
SET [PersonName] = CONCAT(
CONCAT([PersonFirstName],
CHAR(32)), [PersonLastName])
GO
 
-- migration script.
 
ALTER TABLE [dbo].[Person]
DROP COLUMN [PersonFirstName];
GO
 
ALTER TABLE [dbo].[Person]
DROP COLUMN [PersonLastName];
GO

Want to Find out More?

Overview

Overview

Take a quick tour to learn all about the key benefits delivered by dbForge Schema Compare for SQL Server.
All Features

All features

Get acquainted with the rich features and capabilities of the Schema Compare in less than 5 minutes.
Request a demo

Request a demo

If you consider employing the Schema Compare for your business, request a demo to see it in action.
Ready to start using dbForge Schema Compare for SQL Server?
Download a free 30-day trial