Skip to content

Использование предложения 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 я смог сохранить значения удаленных строк с датой и временем их удаления. Это простой пример, но что-то подобное может использоваться для аудита, если подойти к этому с умом.

Trackbacks

No Trackbacks

Comments

Display comments as Linear | Threaded

No comments

The author does not allow comments to this entry

Add Comment

Enclosing asterisks marks text as bold (*word*), underscore are made via _word_.
Standard emoticons like :-) and ;-) are converted to images.

To prevent automated Bots from commentspamming, please enter the string you see in the image below in the appropriate input box. Your comment will only be submitted if the strings match. Please ensure that your browser supports and accepts cookies, or your comment cannot be verified correctly.
CAPTCHA

Form options

Submitted comments will be subject to moderation before being displayed.