Migrating data in SQL Server from source to target with schema change at target

Question

We have a strange scenario.

We have a source database running on SQL Server 2005 where we push data to a target database running on SQL Server 2008 R2.

The source database table has 5 columns. The target database contains an extra column which is not nullable and has a DEFAULT value defined.

We need to push data for the 5 columns from source to target. If we use any tool like red-gate or dbcompare, it will compare source with destination and provide a query to insert the new data on to the target, however running the query fails as it is not inserting anything into the newly introduced column which is not nullable.

Any idea on how to handle this?

• Darin Strait: If the comparison tool does not include the extra column in the generated INSERT statement(s), and there is a default on that column, where is the error message during deployment coming from?
• Eric Higgins: Redgate has two separate tools, one for data (DML) compare, and the regular compare tool is for schema (DDL). To "check the schema difference" use the regular compare tool.
• Rampo: We modified the generated scripts and we move ahead. Thanks for all of the help.

Answer 1

Did you

1. Copied all the data to SQL2008
2. Updated the schema on SQL2008 according to the upgrade script?

Copy data shouln't be the problem here, schema compare neither ;)

For the scheme compare from sql 2005 to 2008 you may use the free Open DbDiff from http://opendbiff.codeplex.com and apply the resulting script to sql 2008.

Otherwise use the new version of the SQL Server Data Tools http://msdn.microsoft.com/en-us/data/tools.aspx that allows SQL 2005 to 2008/2012/Azure migration and scheme update out of Visual Studio 2010+

• Rampo: We modified the generated scripts and we move ahead. Thanks for all of the help.