Categories
How To

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

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s