Move rows between tables in SQL

Question

I have 2 tables, an active table and an inactive table. I want to move rows from the active to the inactive table. My first thought was

insert into inactive select * from active where ...
delete from active active where ...

However about .42 seconds later I noticed this will drop/duplicate rows if updates alter what the where clause selects.

In this case, I can easily prevent that but what should I do in cases where I can't?

edit: From the answers it look like there isn't an easy/trivial way to do this. I'm really surprised by this. I would think that there would be some substantial benefits to having it.

Answer 1

Status flags are your friend.

UPDATE old_data SET move="MARKED";
INSERT INTO somewhere... SELECT where move="MARKED";
DELETE FROM old_data WHERE move="MARKED";

If you do this with Autocommit off, it will seize locks all over the place.

You can COMMIT after each step, if you want to do a little less locking.

Answer 2

(In MS SQL at least) you could use transactions and locking hints:

begin tran

insert into inactive
select * from active with (updlock)
where ...

delete from active
where ...

commit tran

Without locking hint (updlock) deadlocks may occur, if someone does alter records being deleted.

Answer 3

If you can lock the rows pessimistically, that is as you read them to copy them into the inactive table, then no one can change them out from under you.

The method to lock the rows varies by database brand, and you don't specify that in your question.

Answer 4

Declare a local table var and put the values into there, insert them into your inactive table, then delete the rows from the active table that are in your local list.

The transaction idea works just as well though too.

Answer 5

Either use a unique id column like

delete from active where rowid in (select rowid in inactive)

or

delete from active as a 
where exists (select * 
              from inactive 
              where pkfld1=a.pkfld1 
              and pkfld2=a.pkfld2)

Also, don't forget to wrap this process in a transaction.

Good luck.

Answer 6

This is how I preserve my where clauses:

DECLARE @MyTable TABLE
(
  TheKey int PRIMARY KEY
)
--
INSERT INTO @MyTable(TheKey)
SELECT TheKey FROM SourceTable WHERE rows I want
--
INSERT INTO Inactive(fieldlist)
SELECT fieldlist
FROM Active
WHERE TheKey IN (SELECT TheKey FROM @MyTable)
--
DELETE
FROM Active
WHERE TheKey IN (SELECT TheKey FROM @MyTable)

If you need stronger than this, you'll need to look at locking (block out changes during the transaction).

Answer 7

Why do you have two tables, rather than a column for "IsActive"?

Answer 8

Does your database support the OUTPUT Clause? Then this could be a perfect and easy to follow solution for you, couldn't it?

http://msdn.microsoft.com/en-us/library/ms177564.aspx

delete active with (readpast)
output DELETED.*
into inactive
where ...