Column name change in generated script

Richdiet

I read this:
http://www.red-gate.com/supportcenter/C ... zation.htm

Renamed columns

SQL Compare attempts to recognize renamed columns by the similarity of the names and the data types of the columns. When a renamed column is recognized as such, SQL Compare renames the column as appropriate.

However, if the names and data types are very different, SQL Compare may consider the renamed column to be a completely different column. In this case, if ColA in your source database is renamed to ColB in your target database, when SQL Compare creates the synchronization SQL script, ColA will be created in the target database as a new column and ColB will be deleted. To avoid data loss, before you synchronize the databases you must take care to preserve any data in the two columns, and merge them following the synchronization.

However my experience with SQL Compare 9 differs from this - it appears to recognize and highlight that several columns are different in the column comparison window well enough, but then the SQL script it generates deletes these old columns and adds new, blank ones - blowing away the data.

What am I missing here? How can I get SQL Compare to change the name of these columns and preserve the data?

(edit to fix link)

peter.peart

Hi there,

Thanks for your post. SQL Compare matches columns and tables based on the names being the same. If they are pretty much the same (i.e. customer and customers) it has fuzzy mapping logic to treat these 2 as the same.

A lot of the time though the differences are too radical for SQL Compare to recognize and map objects together appropriately, so it treats them as distinctly different objects thus the drop and recreate, as it believes they are individual objects that exist in one DB but not the other.

The workaround is to rename the objects using SQL Prompt PRO so they are identical, and then run SQL Compare. Doing this should then ensure the objects are mapped together appropriately, i.e. identified as the same, and any data should be put into a temp table whilst the schema modifications are being made, and then inserted back into the table. This is of course assuming that the schema modification does not result in a column being dropped.

HTH!

Pete

whenriksen

I could be wrong but I assume that a renamed column would still have the same ordinal number. If the ordinal matches, rename the column. If not, drop/add the columns.

I wasn't aware of this issue and fortunately read through the entire change script before running it.

peter.peart

The dependency engine calculates dependencies on a number of things, including name differences.

It isn't inconceivable that the ordinal could be the same depending on the design of the tables and the data within the columns is radically different.

It's therefore always best to properly analyze what the tool proposes to do. Note that dropped columns will be warned about when proceeding through the sync wizard.

HTH!

Pete

David Atkinson

SQL Compare 9.5 has a table/column mapping feature as well as a migrations feature that allows custom scripts to override default script generation behavior.

For more information visit: http://www.red-gate.com/MessageBoard/vi ... 1312#51312

Kind regards,

David Atkinson
Product Manager
Red Gate Software