How to handle field name changes

• 
  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 D August 12, 2008 15:27

  - 0 +
• 
  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 D August 14, 2008 12:00

  - 0 +

Add comment

Please sign in to leave a comment.