May 19, 2013

Output Clause in SQL Server 2005

SQL Server 2005 introduced a new TSQL feature OUTPUT clause that allows you to retrieve data affected by insert/update/delete statements easily. The  OUTPUT clause returns the data that you've inserted or deleted from the table within the statement.

Basically OUTPUT clause has the access to magic tables (inserted and deleted) like triggers. The OUTPUT clause can be used to return results to the client or consume it on the server into a temporary table or table variable or permanent table  Let us take examples to understand how OUTPUT works.

First create a table Employee
    EmpId VARCHAR(10), EmpName varchar(10), DOJ DATETIME

Example 1: Displaying the data just inserted with output clause within the same insert statement.
OUTPUT inserted.*
SELECT 'EMP001', 'Sandeep', '01/01/2008' UNION ALL
SELECT 'EMP002', 'Abhay', '06/01/2008'

In the above example we have inserted two rows in the Employee table and with OUTPUT clause we are displaying the rows we have just inserted from the same insert statement.

Example 2: Insert the data in other table on insertion and deletion of a table
    EmpId VARCHAR(10), EmpName varchar(10), DOJ DATETIME, Action CHAR(1), LogTime DATETIME
OUTPUT inserted.*, 'I', GETDATE() INTO @TAB
SELECT 'EMP003', 'Ritesh', '09/01/2008'

DELETE Employee
OUTPUT deleted.*, 'D', GETDATE() INTO @TAB
WHERE EmpId = 'EMP003'


In the above example first we have inserted and then deleted a row in the Employee table and with OUTPUT clause we have inserted both the inserted and deleted rows in the table variable from the inserted and deleted tables respectively. 

    Choose :
  • OR
  • To comment
No comments:
Write Comments

Probabilmente come studente universitario ti troverai a scrivere una o più tesi per il tuo analogo esame di fine carriera. Ma di cosa si tratta in particolare? Scopriamo quali sono i pro dei tool per riscrivere testo che troverai online. Per strumento di parafrasi si intende un tool online che si occupa di parafrasare in maniera immediata frasi oppure interi paragrafi. Questa attività si svolgerà interamente su internet e non lascerà alcuna traccia di plagio