Tag: output clause

  • SQL Server 2005 Output Clause

    The OUTPUT clause helps in returning the actual data from a table soon after an insert, update or delete. Output clause uses two virtual tables inserted and deleted. Output clause can be useful to detect actual rows affected by DML statements.


    CREATE TABLE Emp(
    EmpName VARCHAR(9),Age INT,MaritalStatus char(1))

    INSERT INTO Emp VALUES ('Dhvani',20,'M')
    INSERT INTO Emp VALUES ('Nehal',20,'M')
    INSERT INTO Emp VALUES ('Sunil',95,'M')
    INSERT INTO Emp VALUES ('Suvrendu',40,'M')
    INSERT INTO Emp VALUES ('Bill',11,'S')
    INSERT INTO Emp VALUES ('Ram',100,'S')
    INSERT INTO Emp VALUES ('Nirmal',50,'S')
    INSERT INTO Emp VALUES ('R',30,'S')

    Declare @dummyEMp as table (EmpName VARCHAR(9),Age INT,MaritalStatus char(1))

    INSERT INTO Emp (EmpName,Age,MaritalStatus)
    OUTPUT inserted.EmpName,inserted.Age,inserted.MaritalStatus INTO @dummyEMP
    VALUES ('Dhvani',20,'M')

    select * from @dummyEMp

    Declare @dummyEMp as table (EmpName VARCHAR(9),Age INT,MaritalStatus char(1))
    INSERT INTO Emp (EmpName,Age,MaritalStatus)
    OUTPUT inserted.* INTO @dummyEMP
    VALUES ('Deepali',27,'M')
    select * from @dummyEMp

    Declare @dummyEMp as table (EmpName VARCHAR(9),Age INT,MaritalStatus char(1))
    update Emp
    set MaritalStatus = 'M'
    output deleted.* into @dummyEMP
    where EmpName = 'Dhvani'
    select * from @dummyEMp

    Declare @dummyEMp as table (EmpName VARCHAR(9),Age INT,MaritalStatus char(1))
    delete from Emp
    output deleted.* into @dummyEMP
    where Age > 50
    select * from @dummyEMp