insert into <archiveTable> using top in while loop

Question

Hi

I need to archive let say Order table to OrderArchive table using SQL statement

Order table has 10,000 records.

I need to insert into OrderArchive table from Order with top 1000 rows in while loop.

How can I achieve this in SQL Server

Thanks.

Answer 1

while 1=1
begin

insert into OrderArchive
select top 1000 * from Order o
where not exists (select top 1 1 from OrderArchive oa where oa.id = o.id)

if @@rowcount = 0 
break;

end

If you want to delete and insert deleted rows into Archive, there's a better solution:

delete o
output deleted.* into OrderArchive
from Order o
where o.ToDelete = 1