Migrating tables with sp_rename
JonasG
Posts: 11 Bronze 1
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
-
JonasG Posts: 11 Bronze 1Seems to work with a PreScript, in SSMS. When generate script with SqlCompare, you can also verify, before you run.