Column mapping problem

[ChrisGodfree]

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?

[Brian Donahue]

One very long-standing issue with SQL Compare is that it tries to map columns from one side to another when it "thinks" you may lose data.

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.
_________________
Brian Donahue
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521

[RBeaubien]

That's nice, but how do I "unmap" an incorrect mapping? I don't want to have to go back and delete invalid data on 40 databases.
_________________
- Robert Beaubien
- Kool Software

[Brian Donahue]

You can try unmapping it but it will just map itself back automatically. I will let the product management know that you see this as a problem.
_________________
Brian Donahue
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521