SQL Server – OUTPUT CLAUSE – SIMPLE –EXAMPLE

Output clause will return information from or expressions based on each row affected by INSERT, UPDATE, DELETE or MERGE Statement. Output clause has access to inserted and deleted tables. This a very good feature in SQL Server started from 2005.Here I am explaining with a simple example : Inserting a row into Employee Details and displaying  the record details with database user who performed this operation using output clause.

--Declaring a Temp table for Displaying who has inserted the data
CREATE TABLE #tmp
(
name VARCHAR(50),
salary FLOAT,
user_updated VARCHAR(50)
)

GO

INSERT INTO dbo.employeedetails
OUTPUT inserted.name,
inserted.salary,
Suser_name()
INTO #tmp
VALUES ('Manu',
45000)

GO

SELECT *
FROM #tmp

 RESULT:

print

2 thoughts on “SQL Server – OUTPUT CLAUSE – SIMPLE –EXAMPLE

  1. Author’s gravatar

    Hi varun,
    When i executed my procedure to return the SPs from a remote server it errors out like the following…..

    The object name ‘xxx.DB_Main.dbo.sys.procedures’
    contains more than the maximum number of prefixes. The maximum is 3.

  2. Author’s gravatar

    Hi Preethy,

    you can use openrowset function to achieve this.Also please check whether you have access to sys.procedures views.
    Example :
    SELECT a.*
    FROM OPENROWSET(‘SQLNCLI’, ‘Server=Servername;Trusted_Connection=yes;’,
    ‘SELECT name
    sys.procedures
    ‘) AS a;

    Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *


4 − = one