Migrating tables with sp_rename

Need some guide how to think when migrating tables with new pk-column, to next time ;)
Background, in my database there is two tables that need a pk migrate to identity int as pk.

My try was to do it in several scrips, and check in, after each step.
1-DropConstraints.sql | also drop default values
2-MergeTables.sql | rename tables to Old and recreate them
3-CreateConstraints.sql | Restore all constraints and default values
4-CopyOldData.sql | added as post script
5-Procedures.sql | New procedures

Works if i run them in this order, but when using Source Control in SSMS, the tool is smart and try to use alter, and fails of course.

Q: Can I use sp_rename, and if so I'll guess I need to place all sp_rename in a PreScript, and check in?
Q: Is there other ways to migrate tables with incompatible data?

Best Answer

Sign In or Register to comment.