How to handle field name changes

adeidadeid Posts: 2
edited August 14, 2008 12:00PM in SQL Compare Previous Versions
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.

Comments

  • Eddie DEddie D Posts: 1,800 Rose Gold 5
    Thank you for your post into the forum.

    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
    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
  • Eddie DEddie D Posts: 1,800 Rose Gold 5
    In my previous post, I described how SQL Compare will behave, if a column is renamed.

    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
    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
Sign In or Register to comment.