T-SQL Delete Inserted Records

Question

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

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 ...

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

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...

Answer 1

You should do some thing like this:

INSERT INTO "table1" ("column1", "column2", ...)
SELECT "column3", "column4", ...
FROM "table2" 
WHERE ...

DELETE FROM "table1"
WHERE ...

Answer 2

You can use the insert ... output clause to store the ID's of the copied rows in a temporary table. Then you can delete the rows from the original table based on the temporary table.

declare @Table1 table (id int, name varchar(50))
declare @Table2 table (id int, name varchar(50))

insert @Table1 (id,name)
          select 1, 'Mitt'
union all select 2, 'Newt'
union all select 3, 'Rick'
union all select 4, 'Ron'


declare @copied table (id int)

insert  @Table2
        (id, name)
output  inserted.id 
into    @copied
select  id
,       name
from    @Table1
where   name <> 'Mitt'

delete  @Table1
where   id in 
        (
        select  id 
        from    @copied
        )
        
select  *
from    @Table1

Working example at Data Explorer.

Answer 3

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