Заметки Дмитрия Пилюгина о Microsoft SQL Server 

PDF

Периодически в работе возникают задачи копирования данных из связанных таблиц. Например, копировать все заказы и соответствующие позиции заказа одного клиента другому клиенту. Для решения такой задачи без циклов в SQL Server 2008 можно использовать инструкцию MERGE.

Рассмотрим такую ситуацию, у нас есть следующая классическая схема связанных таблиц — клиенты, заказы и позиции заказа:

use tempdb;
go
create table dbo.Customer (
	CustomerID int identity primary key,
	Name varchar(100)
);
create table dbo.[Order] (
	OrderID int identity primary key,
	CustomerID int,
	Date varchar(100)
);
create table dbo.OrderPosition (
	OrderPositionID int identity primary key,
	OrderID int,
	Name varchar(100),
	Quant int
);
go
alter table dbo.[Order] add constraint fk_OrderCustomer foreign key (CustomerID) references dbo.Customer(CustomerID);
alter table dbo.OrderPosition add constraint fk_OrderPositionOrder foreign key (OrderID) references dbo.[Order](OrderID);
go
-- Клиент 1
insert into dbo.Customer(Name) values ('Homer Simpson')
-- Заказы клиента 1
insert into dbo.[Order](CustomerID, Date) values (1, '20110101'), (1, '20110102')
-- Детали заказов клиента 1
insert into dbo.OrderPosition(OrderID, Name, Quant) values (1, 'Duff beer', 5), (1, 'Hotdog', 10)
insert into dbo.OrderPosition(OrderID, Name, Quant) values (2, 'White T-Shirt', 1), (2, 'Blue pants', 2)
-- Клиент 2
insert into dbo.Customer(Name) values ('Ned Flanders')
go

select * from Customer
select * from [Order]
select * from OrderPosition
go

Есть два клиента, требуется написать процедуру, которая бы копировала все заказы с их деталями из одного клиента другому клиенту.

Копирование самих заказов не вызывает проблем. Вопрос возникает, когда нужно копировать в соответствующий «новый» заказ, соответствующие детали «старого» заказа. И вот это «соответствующий» — как раз вызывает вопросы. Как узнать соответствие идентификаторов: «ИД нового заказа» — «ИД старого заказа».

Если заказ всего один, то конечно все понятно, можно получить ИД нового заказа при помощи, например, scope_identity() а ид старого известен и он один – при установлении связи между двумя ИД – никаких разночтений не возникает.
Разночтения начинаются, когда заказов несколько, даже если бы мы узнали при помощи директивы output все ИД «новых» заказов, как узнать какой из них, соответствует какому из «старых»?

При помощи цикла можно все свести к решению для копирования одного заказа, с которым, как известно, нет проблем. Но использование циклов не очень выгодно и не приветствуется в SQL.

Вот если бы можно было бы написать в предложении INSERT такую конструкцию:

declare @CustomerID_src int = 1, @CustomerID_dst int = 2
declare @OrderCopyRelations table(OrderID_old int, OrderID_new int)
-- нерабочий запрос
insert into dbo.[Order](CustomerID, Date)
	output o.OrderID, inserted.OrderID
	into @OrderCopyRelations(OrderID_old, OrderID_new)
select
	CustomerID = @CustomerID_dst,
	o.Date
from
	dbo.[Order] o
where
	o.CustomerID = @CustomerID_src

Но, к сожалению, такой запрос не выполнится, т.к. в предложении OUTPUT инструкции INSERT не разрешается ссылаться на исходную таблицу. Вот как раз в таком случае и поможет MERGE.

При помощи MERGE копирование осуществится так:

declare @CustomerID_src int = 1, @CustomerID_dst int = 2

declare @OrderCopyRelations table(OrderID_old int, OrderID_new int)

/* 1. Копируем заказы с записью связей: "новый ИД" - "старый ИД" */
-- в какую таблицу будем вставлять
merge 
	dbo.[Order] dst
-- какие записи будем вставлять
using (
	select 
		CustomerID = @CustomerID_dst,
		o.Date,
		o.OrderID
	from 
		dbo.[Order] o 
	where 
		o.CustomerID = @CustomerID_src
) src 
-- всегда ложное условие
on 0=1 when not matched then 
-- вставка данных
insert (CustomerID, Date) 
values (CustomerID, Date)
-- вывод связей старых ид и новых в соотв.таблицу
output src.OrderID, inserted.OrderID 
into @OrderCopyRelations(OrderID_old, OrderID_new);

-- посмотрим связи ИД "старых" заказов и "новых"
select * from @OrderCopyRelations

/* 2. На основе таблицы связей, копируем соответствующие позиции заказов*/

-- после того, как связи есть, можем спокойно скопировать 
-- детали заказов в соответствии с заказами
insert dbo.OrderPosition(OrderID, Name, Quant)
select
	ocr.OrderID_new, 
	op.Name, 
	op.Quant
from
	@OrderCopyRelations ocr
	join dbo.OrderPosition op on ocr.OrderID_old = op.OrderID
go

select * from Customer
select * from [Order]
select * from OrderPosition
go

Вот и все! Надеюсь, кому-нибудь пригодится!

use tempdb;
go
drop table dbo.OrderPosition;
drop table dbo.[Order];
drop table dbo.Customer;
go

PDF