SQL drop table and re-create and keep data
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.
- Right click on the desired DB in object explorer
- Tasks > Generate scripts
- Select the table you want to script
- 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 fieldsFROM MyTableDROP TABLE MyTableexec sp_rename 'MyTable2', 'Mytable'
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.