Tag Archives: move

Example: How to MOVE rows from first table to a second table in a single transaction in SQL Server.

-- 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,
)

insert into [dbo].[Foo]( [MyKey], [MyColumn] )
select 1, 11 union all
select 2, 22;

-- Verify the rows before.
select * from [dbo].[Foo];
select * from [dbo].[Bar];

-- NOTE: These move queries create identical execution plans in 2019.

-- 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 [MyKey] = 2;

-- Method 2: Subquery move
insert [dbo].[Bar]( [MyKey], [MyColumn] )
select [MyKey], [MyColumn]
from (
	delete
	from [dbo].[Foo]
	output deleted.*
	where [MyKey] = 2
) [MovedRows]
--where [MovedRows].[MyKey] = 2;
-- Not needed, but it is possible to filter incoming rows.

-- Verify the rows after.
select * from [dbo].[Foo];
select * from [dbo].[Bar];