How to Move Rows from Table A to B and Select Only the Moved Rows from A?

Question

I need to move all existing rows in table A over to table B. I also need to select ONLY the moved rows from table A. (Not from Table B since Table B is an archive and contains a lot of rows which will result in the select taking a lot of time)

I'm using Microsoft SQL Server 2008 and .Net (System.Data.SqlClient)

Please note that records are inserted continuously to Table A. I need to ensure only the moved records are selected and that they are selected from Table A before they are deleted.

What is the most efficient way to do this?

Answer 1

I was finally able to solve this issue by using the SQL Server's OUTPUT clause. Have a look below. IMO I see no reason why this method would hold any unnecessary locks either.

declare @TempTable TABLE(Col1 bigint, Col2 varchar(50))

delete from TableA
output Deleted.Col1, Deleted.Col2 INTO @TempTable

insert into TableB (Col1, Col2)
select Col1, Col2 from @TempTable

select Col1, Col2 from @TempTable

Thanks you all for your help.