How to handle field name changes
adeid
Posts: 2
Hi. I want to use sql Compare API to develop tool for upgrading my application database from version to version. The issue I am having is how to handle fields that have their names changed. I know that Sql Compare API tool can detect newly added fields and deleted fields but what about fields that have their names changed.
Please tell me how to handle this with Sql Compare API tool.
Please tell me how to handle this with Sql Compare API tool.
Comments
If a table column has been renamed. SQL Compare will synchronize the table by issuing two ALTER TABLE statements.
The first ALTER TABLE statement will add new column with the old name.
The second ALTER TABLE statement will drop the column with the new name.
For example: Create a table in each database called DB1 and DB2 using the following:
CREATE TABLE [myTable] (
Col1 INT NOT NULL, Col2 INT NULL, Col3 INT NULL)
Rename Col3 to become ColC in DB2.
When the tables are compared, SQL Compare will know that myTable exists in both databases but are different.
If you synchronizing from DB1 to DB2, SQL Compare will create a synchronization script which contains the following:
BEGIN TRANSACTION
GO
PRINT N'Altering [dbo].[myTable]'
GO
ALTER TABLE [dbo].[department] ADD
[Col3] INT NULL COLLATE Latin1_General_CI_AS NULL
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
ALTER TABLE [dbo].[myTable] DROP
COLUMN [ColC]
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
@TRANCOUNT>0 BEGIN
PRINT 'The database update succeeded'
COMMIT TRANSACTION
END
ELSE PRINT 'The database update failed'
GO
DROP TABLE #tmpErrors
GO
I hope the above helps.
Many Thanks
Eddie
Senior Product Support Engineer
Redgate Software Ltd
Email: support@red-gate.com
Are you asking if a column name mapping feature exists?
Using my example, if there is data in Col3, and it has been renamed to ColC, when the synch happens, SQL Compare will drop the column and its data.
Are you looking for Col3 to be mapped to ColC? If the answer is yes, this action is not possible in SQL Compare at this time.
Many Thanks
Eddie
Senior Product Support Engineer
Redgate Software Ltd
Email: support@red-gate.com