Migrating data in SQL Server from source to target with schema change at target 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?
Did you 


Copied all the data to SQL2008
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+