SQL drop table and re-create and keep data

Question

On our original design we screwed up a foreign key constraint in our table. Now that the table is full of data we cannot change it without dropping all of the records in the table. The only solution I could think of is to create a backup table and put all of the records in there, then delete all the records, alter the table and start adding them back. Any other (BETTER) ideas? Thanks!

Using MS SQL Server

Answer 1

I'm a bit late, just for reference.
If You are using SQL Server Management Studio, You could generate a DROP and RECREATE script with "Keep schema and data" option.

1. Right click on the desired DB in object explorer
2. Tasks > Generate scripts
3. Select the table you want to script
4. Then clicking on Advanced button
   • "Script DROP and CREATE" ->"Script DROP and CREATE"
   • "Types of data to script" -> "Schema and data"

Hope this helps