Сегодня я рассмотрел предложение вывода в SQL Server 2005, позволяющее возвращать данные в переменные таблицы из операторов модификации (INSERT, UPDATE, DELETE). Полезные сценарии для DML с результатами включают очистку и архивирование, приложения для обмена сообщениями и другие сценарии. Синтаксис этого нового предложения OUTPUT:
OUTPUT <dml_select_list> INTO @table_variable
может получить доступ к старому/новому изображению измененной строки, ссылаясь на вставленную и удаленную таблицу, аналогично доступу к триггерам. В инструкции INSERT возможен доступ только к вставленной таблице. В инструкции DELETE можно получить доступ только к удаленным таблицам. В инструкции UPDATE вы можете получить доступ к вставленным и удаленным таблицам. Ниже приведен пример из MSDN:
В качестве примера схемы очистки и архивирования, которая может быть полезна для DML с результатами, предположим, что у вас есть большая таблица Orders и вы хотите регулярно очищать исторические данные. Вы также хотите скопировать очищенные данные в архивную таблицу с именем OrdersArchive. Вы объявляете табличную переменную с именем @DeletedOrders и вводите цикл, в котором удаляете исторические данные частями (например, заказы старше 2003 года). Добавленный здесь код представляет собой предложение OUTPUT, которое копирует все атрибуты всех удаленных строк в табличную переменную @DeletedOrders, а затем использует оператор INSERT INTO для копирования всех строк табличной переменной в таблицу OrdersArchive:
DECLARE @DeletedOrders TABLE
(
идентификатор заказа INT,
дата заказа DATETIME,
эмпидинт,
кустид VARCHAR(5),
количествоINT
)
ПОКА 1=1
НАЧИНАТЬ
НАЧАТЬ ТРАН
УДАЛИТЬ TOP(5000) ИЗ Заказов
ВЫВОД удален.* INTO @DeletedOrders
ГДЕ дата заказа < '20030101'
INSERT INTO Архив заказов
ВЫБЕРИТЕ * ИЗ @DeletedOrders
СОВЕРШИТЬ ТРАНС
УДАЛИТЬ ИЗ @DeletedOrders
ЕСЛИ @@rowcount < 5000
ПЕРЕРЫВ
END
Другой пример: если существует следующая таблица: Создайте таблицу «Сотрудник» (EmpID intidentity(1, 1) ограничение Первичный ключ «Сотрудник_pk», FirstName varchar(100), LastName Varchar(100), Sex char(1), Приветствие в случае, когда Пол = «М», затем «Г-н», когда Пол = «Ж», затем «Мисс», иначе «конец», Заработная плата, Вычеты в случае, когда зарплата <3000, тогда 0,20 * Зарплата, когда зарплата от 30 000 до 50 000 затем .26* Заработная плата, если зарплата находится между 50001 и 75000, затем .30*Зарплатаelse .35*Зарплата в конце, ограничение даты и времени Министерства юстиции DOJ_DEF по умолчанию getdate())
} Очень простая таблица, в которой предложения используются для установки приветствия, вычетов, doj и т. д. соответственно. И мы можем создать еще одну хранимую процедуру следующим образом: Создайте процедуру сотрудника_insert@Fname varchar(100),@Lname varchar(100),@sex char(1),@Salary Moneyasdeclare @empoutput table (EmpID int, FirstName varchar(100) , Фамилия Varchar(100),Символ пола(1), Символ приветствия(3),Заработная плата,Деньги отчислений,Дата-время Министерства юстиции )вставить в Сотрудник(Имя,Фамилия,Зарплата,пол)Вставлено ВЫВОД.* в @empoutputvalues(@Fname) ,@Lname ,@Salary,@sex )Выберите * из @empoutput
Затем при вызове хранимой процедуры вы можете использовать USE [MyDatabase] следующим образом.
GOexec сотрудников_insert 'Miho','Yoshikawa','F',146000
Таким образом, данные будут вставлены в таблицу сотрудников во время выполнения, и будет определена временная переменная empoutput. Обратите внимание, что она может быть в форме таблицы. Определенные в форме, это легко увидеть здесь, и данные, вставленные в таблицу сотрудников, будут одновременно выведены в переменную empoutput. После выполнения этой хранимой процедуры мы обнаружим следующие результаты.
EmpID, Имя, Фамилия, Пол, Приветствие, Заработная плата, Отчисления, Министерство юстиции
7, Михо, Ёсикава, Ф, г-жа, 146000.00,51100.00,2006-04-09 23:16:44.920
, и записи действительно вставляются в таблицу сотрудников
Вы можете увидеть
источник: jackyrong БЛОГ