Today I looked at the output clause in SQL Server 2005 so that you can return data to table variables from modification statements (INSERT, UPDATE, DELETE). Useful scenarios for DML with results include purging and archiving, messaging applications, and other scenarios. The syntax of this new OUTPUT clause is:
OUTPUT <dml_select_list> INTO @table_variable
can access the old/new image of the modified row by referencing the inserted table and deleted table, in a similar way to accessing triggers. In an INSERT statement, only the inserted table can be accessed. In a DELETE statement, only deleted tables can be accessed. In the UPDATE statement, you can access inserted tables and deleted tables. The following is an example from MSDN:
As an example of a purging and archiving scheme that may be useful for DML with results, assume that you have a large Orders table and you want to purge historical data on a regular basis. You also want to copy the purged data to an archive table named OrdersArchive. You declare a table variable named @DeletedOrders and enter a loop in which you delete historical data in chunks (e.g., orders older than 2003). The code added here is the OUTPUT clause, which copies all attributes of all deleted rows to the @DeletedOrders table variable, and then uses the INSERT INTO statement to copy all rows in the table variable to the OrdersArchive table:
DECLARE @DeletedOrders TABLE
(
orderid INT,
orderdate DATETIME,
empidINT,
custid VARCHAR(5),
qtyINT
)
WHILE 1=1
BEGIN
BEGIN TRAN
DELETE TOP(5000) FROM Orders
OUTPUT deleted.* INTO @DeletedOrders
WHERE orderdate < '20030101'
INSERT INTO OrdersArchive
SELECT * FROM @DeletedOrders
COMMIT TRAN
DELETE FROM @DeletedOrders
IF @@rowcount < 5000
BREAK
END
Another example is as follows. If there is a table as follows: Create table Employee(EmpID int identity(1, 1) constraint Employee_pk primary key, FirstName varchar(100), LastName Varchar(100), Sex char(1), Salutation as case when Sex ='M' then 'Mr.' when Sex ='F' then 'Ms.' else '' end, Salary Money, Deductions as case When Salary <3000 then .20 * Salarywhen Salary between 30000 and 50000 then .26* Salary when Salary between 50001 and 75000 then .30*Salaryelse .35*Salary end, DOJ datetime constraint DOJ_DEF default getdate())
} A very simple table, in which case clauses are used to set Salutation, deductions, doj, etc. respectively. And we can create another stored procedure, as follows: Create procedure employee_insert@Fname varchar(100),@Lname varchar(100),@sex char(1),@Salary Moneyasdeclare @empoutput table (EmpID int, FirstName varchar(100) , LastName Varchar(100),Sex char(1), Salutation char(3),Salary Money,Deductions Money,DOJ datetime )insert into Employee(FirstName,LastName,Salary,sex)OUTPUT inserted.* into @empoutputvalues(@Fname ,@Lname ,@Salary,@sex )Select * from @empoutput
Then when calling the stored procedure, you can use USE [MyDatabase] like this
GOexec employee_insert 'Miho','Yoshikawa','F',146000
In this way, data will be inserted into the employee table during execution, and a temporary variable empoutput will be defined. Note that this can be in the form of the table Defined in the form, it is easy to see here, and the data inserted into the employee table will be output to the empoutput variable at the same time. After we execute this stored procedure, we will find the following results
EmpID,FirstName,LastName,Sex,Salutation,Salary,Deductions,DOJ
7,Miho,Yoshikawa,F,Ms.,146000.00,51100.00,2006-04-09 23:16:44.920
, and records are indeed inserted into the employee table
You can see
the source: jackyrong BLOG