删除/截断表中具有外键的记录
这是我的要求，我试图根据 Adventure 作品数据库来详细说明这一点。
我必须删除具有约束和外键关系的 Production.Product 表的所有记录，并填充其他表中的数据（当前我正在使用此语句复制表中的数据）
SELECT * INTO Product_temp
FROM [AdventureWorks].[Production].[Product]
所以我必须删除 Product 表中的所有记录和然后从 product_temp 插入。
我找到了这个答案，但我无法在 SQL SERVER 2005
This is my requirement, I am trying to elaborate that in terms of Adventure works database.
I have to delete all records of Production.Product table which has constraints and Foreign key relation and fill the data from other table (currently I am using this statement to copy data in the table)
SELECT * INTO Product_temp
FROM [AdventureWorks].[Production].[Product]
So I have to delete all records from Product table and then insert from product_temp.
I have found this answer, but I am unable to find this option in SQL SERVER 2005
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
如果您能够对所有四个表进行批量替换，那么最快的策略是删除所有四个表中的所有行并重新插入，而不是执行“Upsert”（好术语@marc_s）。那里的一个建议是：我建议使用 TRUNCATE 来清除表，而不是 DELETE。这表现得更好，因为引擎避免将所有这些已删除的记录写入事务日志。显然，您需要首先删除“边缘”表，即那些与其他表有 FK 的表。
如果您发现表具有循环引用，使得很难从表中删除行，那么我会这样做：
If you are able to do a wholesale replace of all four tables then your quickest strategy is to delete all rows from all four table and re-insert rather than doing an "Upsert" (good term @marc_s). One recommendation there: I'd recommend using TRUNCATE to clear out your tables rather that DELETE. This performs much better because the engine avoids writing all those deleted records into the transaction log. Obviously you'd need to delete your "fringe" tables first, that ones that have FK's to other tables.
If you find that the tables have circular references that make it hard to delete rows from tables then here's what I'd do:
您不需要删除外键约束。
2.EXEC sp_MSforeachtable 'UPDATE STATISTICS ? AND FULLSCAN'
3.Exec sp_MSforeachtable "dbcc dbreindex('?')"
You don't need to remove the foreign key constraint.
2.EXEC sp_MSforeachtable 'UPDATE STATISTICS ? WITH FULLSCAN'
3.Exec sp_MSforeachtable "dbcc dbreindex('?')"