Column mapping problem

Compares and synchronizes SQL Server databases, backups and scripts.

Moderators: JonathanWatts, Chris Auckland, David Atkinson, eddie davis, Anu Deshpande, Michelle Taylor, alice.easey, james.billings, chengvoon.tong

Column mapping problem

Postby ChrisGodfree » Wed Jan 16, 2013 9:31 am

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?
ChrisGodfree
 
Posts: 7
Joined: Thu May 10, 2012 9:02 am

Postby Brian Donahue » Fri Jan 18, 2013 5:25 pm

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
 
Posts: 6669
Joined: Mon Aug 23, 2004 10:48 am

Postby RBeaubien » Mon Feb 04, 2013 8:00 am

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
RBeaubien
 
Posts: 19
Joined: Mon Jun 25, 2007 6:13 pm

Postby Brian Donahue » Mon Feb 04, 2013 10:22 am

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
 
Posts: 6669
Joined: Mon Aug 23, 2004 10:48 am

Postby Niall » Thu May 15, 2014 11:14 am

Has the auto mapping thing been made optional yet? This causes us endless issues and we almost never rename columns - being a datawarehouse. It is one of the most irritating features in SQL Compare.
Niall
 
Posts: 20
Joined: Wed Jul 21, 2010 9:15 am


Return to SQL Compare 10

Who is online

Users browsing this forum: No registered users and 0 guests

cron