如何在一个事务中移动记录?或者SQL中的生产者-消费者模式

Surprisingly can't find a match for my question. I have one table that I need to use a source of data to insert to another and then delete whatever has been inserted. This has to be accomplished in blocking manner, i.e. if the same query/SP is executed at the same time, the same records must not be moved thus creating duplicates. Surprisingly can't find a match for my question