-- 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];
Protiguous 