-- NOTE: This is an EXAMPLE to show it is possible -- to achieve moving rows using an implicit transaction -- from one table into another table. -- Set up example tables, Foo & Bar. drop table if exists [dbo].[Bar]; drop table if exists [dbo].[Foo]; create table [dbo].[Bar]( [ID] [int] identity(1,1) not null primary key clustered, [MyKey] [int] null index ix_mykey, [MyColumn] [int] null index ix_mycol ) create table [dbo].[Foo]( [ID] [int] identity(1,1) not null primary key clustered, [MyKey] [int] null index ix_mykey, [MyColumn] [int] index ix_mycol, ) GO -- Create some dummy data insert into [dbo].[Foo]( [MyKey], [MyColumn] ) values (1, 11), (2, 22), (3, 33), (4, 44); GO 10240 -- Verify the rows before. select count(*) from [dbo].[Foo]; select count(*) from [dbo].[Bar]; -- Move rows from table Foo into table Bar using two styles. -- Method 1: "Direct" move delete from [dbo].[Foo] output [deleted].[MyKey], [deleted].[MyColumn] into [dbo].[Bar]( [MyKey], [MyColumn] ) where [ID] % 3 = 1; -- Method 2: "Subquery" move insert [dbo].[Bar]( [MyKey], [MyColumn] ) select [MyKey], [MyColumn] from ( delete from [dbo].[Foo] output deleted.* where [ID] % 3 = 2 ) [MovedRows] where [MovedRows].[MyKey] = 2; -- Note: The last where is not needed, just showing it is possible to re-filter incoming [from the deleted output] rows. -- Verify the counts after. -- Note: Any deleted rows filtered out via Method 2 are now gone. select count(*) from [dbo].[Foo]; select count(*) from [dbo].[Bar];