SQL SQL删除表并重新创建并保留数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6033210/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL drop table and re-create and keep data
提问by user516883
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
使用 MS SQL Server
采纳答案by CristiC
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.
创建备份表，清空原表，修改表，然后一步一步插入，直到找到违规。
回答by voodoo_patch
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.
来晚了，仅供参考。
如果您使用的是 SQL Server Management Studio，则可以使用“保留架构和数据”选项生成 DROP 和 RECREATE 脚本。
- 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
希望这可以帮助
回答by JNK
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'
回答by Zarepheth
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.
使用 SQL Server Management Studio (SSMS)，您可以使用它的表设计器来指定表的最终条件。在保存更改之前，让它生成更改脚本并保存该脚本。退出设计窗口，打开脚本并查看它。SSMS 可能已经生成了一个脚本来完成您需要的一切，修复主外键关系，同时保留所有现有数据。如果没有，您将拥有一个已经启动的脚本，该脚本执行您需要执行的大部分操作，并且应该能够根据您的需要对其进行修改。
