T-SQL删除插入的记录 [英] T-SQL Delete Inserted Records

问题描述

我知道标题可能看起来很奇怪，但这是我想要做的:

I know the title may seem strange but this is what I want to do:

1. 我的桌子上有很多记录.

2. 我想获取一些记录并将其插入其他表中.像这样:

1. I have table with many records.

2. I want to get some of this records and insert them in other table. Something like this:

INSERT INTO TableNew SELECT * FROM TableOld WHERE ...

棘手的部分是，我也希望从原始表中删除我插入的这些行.

The tricky part is that I want this rows that I have inserted to be deleted form the origin table as well.

是否有一种简单的方法来执行此操作，因为我唯一设法做到的就是使用临时表保存所选记录，然后将它们放入第二个表并从中删除与它们匹配的行第一张桌子.这是一个解决方案，但是有这么多记录(超过300万个一半)，我正在寻找其他想法...

Is there a easy way to do this, because the only think that I have managed to do is to use a temporary table for saving the selected records and then to put them in the second table and delete rows that match with them from the first table. It is a solution, but with so many records (over 3 millions and half) I am looking for some other idea...

推荐答案

在2005+版本中，请使用OUTPUT子句，如下所示:

In 2005+ use OUTPUT clause like this:

DELETE FROM TableOld 
OUTPUT DELETED.* INTO TableNew
WHERE YourCondition

它将在单笔交易中执行，既可以完成也可以同时回滚

It will be performed in single transaction and either completed or roll back simultaneously

这篇关于T-SQL删除插入的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！