Merging tables

When you merge tables in a database schema, it is necessary to create a new table and delete the original table. In this case, the deployment will lead to data loss of the dropped table.

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

Add a column to the sample table Person
Generate a migration script to copy data from the original table to the new table and execute it
Drop 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 [dbo].[Person] ( [PersonNumber] [INT] PRIMARY KEY, [PersonName] [NVARCHAR](50) NULL, [PersonAddress] [NVARCHAR](100) NULL ) GO	CREATE TABLE [dbo].[Person] ( [PersonNumber] [INT] PRIMARY KEY, [PersonName] [NVARCHAR](50) NULL ) GO INSERT INTO [dbo].[Person] ([PersonNumber], [PersonName]) VALUES (74, N'Alan Dou') INSERT INTO [dbo].[Person] ([PersonNumber], [PersonName]) VALUES (75, N'Jordan Sanders') GO CREATE TABLE [dbo].[PersonAddress] ( [AddressID] [INT] IDENTITY, [PersonNumber] [INT] NOT NULL, [AddressLine] [NVARCHAR](50) NULL, [State] [NVARCHAR](50) NULL, [Zip] [NVARCHAR](50) NULL ,PRIMARY KEY CLUSTERED ([AddressID]) ) ON [PRIMARY] GO ALTER TABLE [dbo].[PersonAddress] ADD FOREIGN KEY ([PersonNumber]) REFERENCES [dbo].[Person] ([PersonNumber]) GO INSERT [dbo].[PersonAddress] ([PersonNumber], [AddressLine], [State], [Zip]) VALUES (74, N'2590 Main St. Bernalillo, Albuquerque', N'New Mexico', N'87102') INSERT [dbo].[PersonAddress] ([PersonNumber], AddressLine, [State], [Zip]) VALUES (75, N'1234 Broadway Street, Apt. 77', N'New York', N'10101') GO

Source table script example

Target table script example

CREATE TABLE [dbo].[Person] (
[PersonNumber] [INT] PRIMARY KEY,
[PersonName] [NVARCHAR](50) NULL,
[PersonAddress] [NVARCHAR](100) NULL
)
GO

CREATE TABLE [dbo].[Person] (
[PersonNumber] [INT] PRIMARY KEY,
[PersonName] [NVARCHAR](50) NULL
)
GO
 
INSERT INTO [dbo].[Person] ([PersonNumber], [PersonName])
VALUES (74, N'Alan Dou')
INSERT INTO [dbo].[Person] ([PersonNumber], [PersonName])
VALUES (75, N'Jordan Sanders')
GO
 
CREATE TABLE [dbo].[PersonAddress] (
[AddressID] [INT] IDENTITY,
[PersonNumber] [INT] NOT NULL,
[AddressLine] [NVARCHAR](50) NULL,
[State] [NVARCHAR](50) NULL,
[Zip] [NVARCHAR](50) NULL
,PRIMARY KEY CLUSTERED ([AddressID])
) ON [PRIMARY]
GO
 
ALTER TABLE [dbo].[PersonAddress]
ADD FOREIGN KEY ([PersonNumber]) REFERENCES [dbo].[Person] ([PersonNumber])
GO
 
INSERT [dbo].[PersonAddress] ([PersonNumber], [AddressLine], [State], [Zip])
    VALUES (74, N'2590 Main St. Bernalillo, Albuquerque', N'New Mexico', N'87102')
INSERT [dbo].[PersonAddress] ([PersonNumber], AddressLine, [State], [Zip])
    VALUES (75, N'1234 Broadway Street, Apt. 77', N'New York', N'10101')
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
DROP TABLE [dbo].[PersonAddress] GO ALTER TABLE [dbo].[Person] ADD [PersonAddress] [NVARCHAR](100) NULL GO	ALTER TABLE [dbo].[Person] ADD [PersonAddress] [NVARCHAR](100) NULL; GO -- migration script: UPDATE [dbo].[Person] SET [dbo].[Person].[PersonAddress] = CONCAT(CONCAT(CONCAT(CONCAT( [AddressLine], ', '), [State]), ', '), [Zip]) FROM [dbo].[PersonAddress] WHERE [Person].[PersonNumber] = [PersonAddress].[PersonNumber] GO -- migration script. DROP TABLE [dbo].[PersonAddress]; GO

Script generated by the Schema Comparison engine

Script that should be generated manually for errorless data migration

DROP TABLE [dbo].[PersonAddress]
GO
 
ALTER TABLE [dbo].[Person]
    ADD [PersonAddress] [NVARCHAR](100) NULL
GO

ALTER TABLE [dbo].[Person]
ADD [PersonAddress]
[NVARCHAR](100) NULL;
GO
 
-- migration script:
UPDATE [dbo].[Person]
SET [dbo].[Person].[PersonAddress] =
CONCAT(CONCAT(CONCAT(CONCAT(
[AddressLine], ', '),
[State]), ', '), [Zip])
FROM [dbo].[PersonAddress]
WHERE [Person].[PersonNumber] =
[PersonAddress].[PersonNumber]
GO
-- migration script.
 
DROP TABLE [dbo].[PersonAddress];
GO

Want to Find out More?

Download a free 30-day trial

Merging tables

Want to Find out More?

Overview

All features

Request a demo