SQL drop table and recreate and save data

Question

In our original design, we have fixed the foreign key constraint in our table. Now that the table is filled with data, we cannot change it without discarding all records in the table. The only solution I could think of was to create a backup table and put all the records there, then delete all the records, change the table and start adding them back. Any other (BEST) ideas? Thanks!

Using MS SQL Server

Answer 1

This is your only solution.

Create a backup table, clear the original one, modify the table and insert step by step until you find a violation.

Answer 2

Here is some kind of pseudo code. No need to create a backup table, just create a new table with the necessary restriction, insert your entries in it and rename it.

CREATE TABLE MyTable_2 (...field definitions) <add the constraint to MyTable_2> INSERT INTO MyTable_2 (fields) SELECT fields FROM MyTable DROP TABLE MyTable exec sp_rename 'MyTable2', 'Mytable' 

Answer 3

Using SQL Server Management Studio (SSMS), you can use its table designer to determine the final state of the table. Before saving the changes, create a script change and save the script. Cancel the design window, open the script and view it. SSMS may already have generated a script that does everything you need, fixing the primary-foreign key relationship while saving all existing data. If not, you will have a script already running that does most of what you need to do, and should be able to modify it for your needs.

Answer 4

I'm a little late, just for reference.
If you are using SQL Server Management Studio, you can create a DROP and RECREATE script with the option "Save Schema and Data."

• Right-click the database in the object explorer.
• Tasks> Generate Scripts
• Select table for script
• Then click the "Advanced" button.
  • "Script DROP and CREATE" → "Script DROP and CREATE"
  • Data types script "->" Schema and data "

Hope this helps