SQL drop table and re-create and keep data

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 droppi开发者_C百科ng 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

---

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

---

Here's some pseudo-code. No need to make a backup table, just make a new table with the right constraint, insert your records into it, and rename.

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'

---

Using SQL Server Management Studio (SSMS), you can use it's table designer to specify the final condition of the table. Before saving the changes, have it generate the change script and save that script. Cancel out of the design window, open the script and review it. SSMS may already have generated a script that does everything you need, fixing the primary-foreign key relationship while preserving all existing data. If not, you will have a script, already started, that performs most of what you need to do and should be able to modify it for your needs.

---

This is your only solution.

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

---

Update All Schema Database Old by new Schema Database .

1. Create script (Right click on the desired DB in object explorer Tasks > Generate scripts -> select option select specific database objects and tables ->next -> advanced-> option Type of data to script Data only -> ok ->next ->next.) to data only and backup Database to old database
2. Drop database old and create new database and make new DB is empty .
3. Excute script of Old Data only on new database .