Red Gate forums :: View topic - Column mapping problem
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Compare 10
SQL Compare 10 forum

Column mapping problem

Search in SQL Compare 10 forum
Post new topic   Reply to topic
Jump to:  
Author Message
ChrisGodfree



Joined: 10 May 2012
Posts: 7

PostPosted: Wed Jan 16, 2013 9:31 am    Post subject: Column mapping problem Reply with quote

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?
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6676

PostPosted: Fri Jan 18, 2013 5:25 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
RBeaubien



Joined: 25 Jun 2007
Posts: 19

PostPosted: Mon Feb 04, 2013 8:00 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6676

PostPosted: Mon Feb 04, 2013 10:22 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Niall



Joined: 21 Jul 2010
Posts: 20

PostPosted: Thu May 15, 2014 11:14 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group