Использование предложения OUTPUT (для операторов Insert и Delete)
Пересказ статьи Mat Richardson. OUTPUT Clause Basics (For Inserts and Deletes)
При обработке сложных операторов insert, update и delete часто полезно знать, что изменилось - для аудита или чтобы вернуть значение в вызывающее приложение (например, ID или другое значение после вставки). Предложение OUTPUT в T-SQL позволяет вам сделать именно это, что действительно очень просто. Я покажу вам как!
Давайте сначала создадим новую таблицу:
if object_id('tempdb..#outputexample') is not null drop table #outputexample;
go
create table #outputexample
(
WidgetID int identity(1,1),
WidgetName varchar(200),
WidgetStatus varchar(10)
);
Теперь вставим в нее одну строку и используем предложение OUTPUT для возврата этой строки:
insert #outputexample
output inserted.*
values
('Thingummybob','In Production');
В этом сильно упрощенном примере, как вы видите, мы добавили предложение OUTPUT непосредственно после предложения INSERT, но перед фактическим изменением, которое мы хотели произвести в таблице. Обратите также внимание на ключевое слово 'inserted', указывающее, что мы хотели посмотреть на вставленные строки. Последующая звездочка (*) указывает, что возвращаться будут все столбцы (аналогично SELECT *).
Мы можем конкретизировать столбцы, которые хотим получить:
insert #outputexample
output inserted.WidgetID, inserted.WidgetStatus
values
('Thingummybob','In Production');
Как и следовало ожидать, это дает нам столбцы WidgetID и WidgetStatus, которые содержат значения, добавленные в таблицу.
Как видите, предложение output довольно похоже на применение оператора SELECT к элементам, которые были изменены в рассматриваемой таблице. Теперь посмотрим, как это может работать для удаления. Для этого примера давайте добавим несколько элементов в таблицу, чтобы было с чем работать:
insert #outputexample
values
('Long Stand','In Production'),
('Tartan Paint','For Sale'),
('Glass Hammer','Discontinued'),
('Left Handed Screwdriver','Discontinued'),
('Elbow Grease','For Sale'),
('Sky Hooks','Discontinued');
Теперь я собираюсь удалить все элементы ‘Discontinued’ и использовать предложение OUTPUT для возвращения списка удаленных строк:
delete from #outputexample
output deleted.*
where WidgetStatus = 'Discontinued';
Тут снова предложение OUTPUT располагается сразу после предложения DELETE, но перед условиями удаления, и имеет префикс с ключевым словом ‘deleted’ у звездочки (*). Это также весьма похоже на написание 'SELECT *' из удаленных строк таблицы. Мы снова могли бы заменить deleted.* списком имен столбцов, или даже при желании вычисляемыми столбцами с алиасами.
Этот метод имеет еще одну полезную функцию. При желании вы можете осуществлять вывод в другую таблицу. Вот пример:
if object_id('tempdb..#outputexample') is not null drop table #outputexample;
if object_id('tempdb..#deleteditems') is not null drop table #deleteditems;
go
create table #outputexample
(
WidgetID int identity(1,1),
WidgetName varchar(200),
WidgetStatus varchar(20)
);
create table #deleteditems
(
WidgetID int,
WidgetName varchar(200),
WidgetStatus varchar(20),
DeletedDateTime datetime
);
insert #outputexample
values
('Long Stand','In Production'),
('Tartan Paint','For Sale'),
('Glass Hammer','Discontinued'),
('Left Handed Screwdriver','Discontinued'),
('Elbow Grease','For Sale'),
('Sky Hooks','Discontinued');
delete from #outputexample
output deleted.*, getdate()
into #deleteditems
where WidgetStatus = 'Discontinued';
select * from #deleteditems;
Здесь я создал таблицу для хранения удаленных элементов, включая дату и время удаления. С помощью предложения output я смог сохранить значения удаленных строк с датой и временем их удаления. Это простой пример, но что-то подобное может использоваться для аудита, если подойти к этому с умом.
Обратные ссылки
Автор не разрешил комментировать эту запись
Комментарии
Показывать комментарии Как список | Древовидной структурой