ApexSQL Knowledgebase: Mapping in ApexSQL Diff, Part 3: Columns

LINKS

General

Apex SQL Audit
Apex SQL Clean
Apex SQL Code
Apex SQL Diff
Apex SQL Doc
Apex SQL Edit
Apex SQL Enforce
Apex SQL Log
Apex SQL Script

Knowledgebase Home

SITE FEED

Site Feed

Support Forum

The fastest and most effective to get answers to your questions quickly. Click Here for more info.

FAQs

Quick answers to common questions. Click Here for more info.

Troubleshooting

Known issues and basic troubleshooting techniques for problems or unexpected behaviors. Click Here for more info.

Knowledgebase

Technical tips, How-to articles, and other tutorials about ApexSQL Tools. Click Here for more info.

Online Documentation

All of our help files for download or convenient viewing online. Click Here for more info.

Videos

Watch product demos, training videos, or tutorials of our products' main features. We frequently upload new videos so check often for updates. Click Here for more info.

ApexSQL Knowledgebase

Tips and How-to Articles for Apex SQL Tools

Mapping in ApexSQL Diff, Part 3: Columns

DESCRIPTION
This is the third and last installment in the three-article KB series discussing ApexSQL Diff's mapping ability. With ApexSQL Diff, you can map users/schemas, tables, and columns. Here, we discuss how to map columns.

What does "mapping columns" mean?
As mentioned in Mapping Schemas, ApexSQL Diff runs in two different modes: Structure mode compares and syncs database structures and Data mode compares and syncs data.

Mapping columns applies only to Data mode.

By default, ApexSQL Diff automatically maps columns of the same name in tables of the same name owned by schemas having the same name. That is, when you have a column named "Col1" in a table named "Table" in a schema named "Schema1" in both the source and destination of your project, these columns will be automatically mapped by ApexSQL Diff.

When the schemas in both source and destination differ in their names, ApexSQL Diff will not automatically map them. This is the case even when the object names in the schemas are all equal; the tables will still be unmapped. To address this, ApexSQL Diff allows table mapping.

The same logic applies for columns. Even when schema and table names are equal, when column names are not, the columns won't automatically be mapped.

Mapping columns enable you to compare objects that belong to the same or different schemas and tables.

Mapping columns
To reproduce the situation, let's issue the following batch:

USE MASTER
CREATE DATABASE BOSTON
GO
USE BOSTON
GO
CREATE SCHEMA BOSTON_USERS AUTHORIZATION DBO
GO

IF OBJECT_ID('Boston_Users.Employees') > 0
DROP TABLE BOSTON_USERS.EMPLOYEES

CREATE TABLE [BOSTON_USERS].[EMPLOYEES](
[EMPLOYEEID_Z] [INT] IDENTITY(1,1) NOT NULL,
[LASTNAME_Z] [NVARCHAR](20) COLLATE LATIN1_GENERAL_CI_AS NOT NULL,
[FIRSTNAME_Z] [NVARCHAR](10) COLLATE LATIN1_GENERAL_CI_AS NOT NULL,
[TITLE_Z] [NVARCHAR](30) COLLATE LATIN1_GENERAL_CI_AS NULL,
[TITLEOFCOURTESY_Z] [NVARCHAR](25) COLLATE LATIN1_GENERAL_CI_AS NULL,
[BIRTHDATE] [DATETIME] NULL,
[HIREDATE] [DATETIME] NULL,
[ADDRESS] [NVARCHAR](60) COLLATE LATIN1_GENERAL_CI_AS NULL,
[CITY] [NVARCHAR](15) COLLATE LATIN1_GENERAL_CI_AS NULL,
[REGION] [NVARCHAR](15) COLLATE LATIN1_GENERAL_CI_AS NULL,
[POSTALCODE] [NVARCHAR](10) COLLATE LATIN1_GENERAL_CI_AS NULL,
[COUNTRY] [NVARCHAR](15) COLLATE LATIN1_GENERAL_CI_AS NULL,
[HOMEPHONE] [NVARCHAR](24) COLLATE LATIN1_GENERAL_CI_AS NULL,
[EXTENSION] [NVARCHAR](4) COLLATE LATIN1_GENERAL_CI_AS NULL,
[PHOTO] [IMAGE] NULL,
[NOTES] [NTEXT] COLLATE LATIN1_GENERAL_CI_AS NULL,
[REPORTSTO] [INT] NULL,
[PHOTOPATH] [NVARCHAR](255) COLLATE LATIN1_GENERAL_CI_AS NULL,
CONSTRAINT [PK_EMPLOYEES] PRIMARY KEY CLUSTERED
(
[EMPLOYEEID_Z] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
ALTER TABLE [BOSTON_USERS].[EMPLOYEES] WITH NOCHECK ADD CONSTRAINT
[FK_EMPLOYEES_EMPLOYEES] FOREIGN KEY([REPORTSTO])
REFERENCES [BOSTON_USERS].[EMPLOYEES] ([EMPLOYEEID_Z])
GO
ALTER TABLE [BOSTON_USERS].[EMPLOYEES] CHECK CONSTRAINT
[FK_EMPLOYEES_EMPLOYEES]
GO
ALTER TABLE [BOSTON_USERS].[EMPLOYEES] WITH NOCHECK ADD CONSTRAINT
[CK_BIRTHDATE] CHECK (([BIRTHDATE]

The above batch will create a new database named Boston and then switch the database context from master to this new database. Within the Boston database, we create the schema Boston_Users and then copy the structure of the Northwind.dbo.Employees table to the new Boston_Users.Employees table. We also modify the Northwind table a bit by appending a "_z" to the column names. Now, we have two tables with identical table names and partially different column names.

We now switch to ApexSQL Diff and create a new data comparison project. As source for the comparison, we choose the Northwind database. For the destination database, we use the Boston database. In the Data Comparison Wizard, we go to the Mapping Columns page where we can select the columns to be matched. The screen will look like the one below:

Let's focus on "Columns" of the above grid:

Opening the list displays the following screen:

The list is divided into two parts. The upper part displays the mapped column pairs. The lower part displays the unmapped column pairs. By default, ApexSQL Diff automatically has mapped the columns with the same name. These pairs are shown in the upper part of the list. The lower part contains the columns that were modified earlier:

As with every Source/Destination visual control element in ApexSQL Diff, on the left side you can see the information pertaining to the Source side of the comparison. The right side shows the information pertaining to the Destination side of the comparison.

Unmapped columns will be ignored by ApexSQL Diff during a Data Comparison Project. Thus, they will not be synchronized. In order to include these column in the comparison and sychronization, map the columns as you would map tables. Select the corresponding "Destination Name" from the dropdown box of that field:

In the above example, "EmployeeID" is mapped to ":EmployeeID_z". It can now be found in the upper part where all other mapped items already are:

Once all the unmapped columns are mapped, the result is as below:

The lower part is empty as all previously unmapped columns are now mapped. All columns in both tables will now be included in the comparison
and synchronization process.

SEE ALSO
Mapping in ApexSQL Diff, Part 1: Schemas
Mapping in ApexSQL Diff, Part 2: Tables

AUTHOR
Frank Kirkland

LAST REVIEW DATE
30 August 2007

Labels: Apex SQL Diff

posted by Janice Lee on Saturday, September 01, 2007

0 Comments: