Column mapping problem
ChrisGodfree
Posts: 11
Hi,
We have come across a problem in the way SQL Compare maps columns which are being renamed which has resulted in data being moved to the wrong fields:
CREATE TABLE [dbo].[Old_Table_Structure]
(
[GoldenSourceValueID] [int] NOT NULL IDENTITY(1, 1),
[BusinessKey] [nvarchar] (100),
[All_TableSchema] [nvarchar] (100),
[Location Code] [nvarchar] (255),
[Description] [nvarchar] (255),
[Depot Code] [nvarchar] (255),
[FloorNo] [nvarchar] (255),
[Address 1] [nvarchar] (255),
[Address 2] [nvarchar] (255),
[Address 3] [nvarchar] (255),
[City] [nvarchar] (255),
[Postal] [nvarchar] (255),
[LastChangedBy] [nvarchar] (255),
[LastChangedDate] [datetime] NOT NULL)
CREATE TABLE [dbo].[New_Table_Structure]
(
[GoldenSourceValueID] [int] NOT NULL IDENTITY(1, 1),
[BusinessKey] [nvarchar] (100),
[All_TableSchema] [nvarchar] (100),
[All_TableName] [nvarchar] (100),
[All_ColumnName] [nvarchar] (100),
[Location Code_old] [nvarchar] (255),
[Description_old] [nvarchar] (255),
[Depot Code_old] [nvarchar] (255),
[FloorNo_old] [nvarchar] (255),
[Address 1_old] [nvarchar] (255),
[Address 2_old] [nvarchar] (255),
[Address 3_old] [nvarchar] (255),
[City_old] [nvarchar] (255),
[Postal_old] [nvarchar] (255),
[LastChangedBy] [nvarchar] (255),
[LastChangedDate] [datetime] NOT NULL)
When deploying the changes through a Command Line, SQL Compare:
1) creates a temporary table with the new structure
2) inserts data to the temporary table from the existing table
3) deletes the existing table
4) renames the temporary table
The problem occurs in step 2 where the columns are incorrectly mapped:
INSERT INTO [dbo].[Temporary_Table]([GoldenSourceValueID], [BusinessKey], [ParentID], [All_TableSchema], [Location Code_old], [Description_old], [Depot Code_old], [FloorNo_old], [Address 1_old], [Address 2_old], [Address 3_old], [City_old], [Postal_old], [LastChangedBy], [LastChangedDate]) SELECT [GoldenSourceValueID], [BusinessKey], [ParentID], [All_TableSchema], [Location Code], [Description], [Depot Code], [FloorNo], [Address 1], [City], [Address 3], [Postal], [Address 2], [LastChangedBy], [LastChangedDate] FROM [dbo].[Existing_Table]
- Address 2_Old is mapped to City, City_Old is mapped to Postal and Postal__Old is mapped to Address 2
We cannot see any logic to why these three fields are mapped incorrectly and no others are impacted - has anyone seen this before or know how these fields are being mapped?
We have come across a problem in the way SQL Compare maps columns which are being renamed which has resulted in data being moved to the wrong fields:
CREATE TABLE [dbo].[Old_Table_Structure]
(
[GoldenSourceValueID] [int] NOT NULL IDENTITY(1, 1),
[BusinessKey] [nvarchar] (100),
[All_TableSchema] [nvarchar] (100),
[Location Code] [nvarchar] (255),
[Description] [nvarchar] (255),
[Depot Code] [nvarchar] (255),
[FloorNo] [nvarchar] (255),
[Address 1] [nvarchar] (255),
[Address 2] [nvarchar] (255),
[Address 3] [nvarchar] (255),
[City] [nvarchar] (255),
[Postal] [nvarchar] (255),
[LastChangedBy] [nvarchar] (255),
[LastChangedDate] [datetime] NOT NULL)
CREATE TABLE [dbo].[New_Table_Structure]
(
[GoldenSourceValueID] [int] NOT NULL IDENTITY(1, 1),
[BusinessKey] [nvarchar] (100),
[All_TableSchema] [nvarchar] (100),
[All_TableName] [nvarchar] (100),
[All_ColumnName] [nvarchar] (100),
[Location Code_old] [nvarchar] (255),
[Description_old] [nvarchar] (255),
[Depot Code_old] [nvarchar] (255),
[FloorNo_old] [nvarchar] (255),
[Address 1_old] [nvarchar] (255),
[Address 2_old] [nvarchar] (255),
[Address 3_old] [nvarchar] (255),
[City_old] [nvarchar] (255),
[Postal_old] [nvarchar] (255),
[LastChangedBy] [nvarchar] (255),
[LastChangedDate] [datetime] NOT NULL)
When deploying the changes through a Command Line, SQL Compare:
1) creates a temporary table with the new structure
2) inserts data to the temporary table from the existing table
3) deletes the existing table
4) renames the temporary table
The problem occurs in step 2 where the columns are incorrectly mapped:
INSERT INTO [dbo].[Temporary_Table]([GoldenSourceValueID], [BusinessKey], [ParentID], [All_TableSchema], [Location Code_old], [Description_old], [Depot Code_old], [FloorNo_old], [Address 1_old], [Address 2_old], [Address 3_old], [City_old], [Postal_old], [LastChangedBy], [LastChangedDate]) SELECT [GoldenSourceValueID], [BusinessKey], [ParentID], [All_TableSchema], [Location Code], [Description], [Depot Code], [FloorNo], [Address 1], [City], [Address 3], [Postal], [Address 2], [LastChangedBy], [LastChangedDate] FROM [dbo].[Existing_Table]
- Address 2_Old is mapped to City, City_Old is mapped to Postal and Postal__Old is mapped to Address 2
We cannot see any logic to why these three fields are mapped incorrectly and no others are impacted - has anyone seen this before or know how these fields are being mapped?
Comments
It used to use a partial column name match as a guide to do this, but it seems the algorithm has got a bit more sophistocated in the intervening years -- I think if it sees columns that are the same ordinal and the same datatype it tries to map them too...
I suppose the reason this is still in the software is for a few reasons, mainly SQL Compare cannot reliably detect column renames, and we got lots of flack back in the early days when SQL Compare dropped columns as a result of a column rename. Also, this is probably not considered a big deal because it won't have any ill-effects except the performance hit of copying data that doesn't really need to be copied.
- Kool Software