• Implementing the output clause in SQL Server

    Posted on July 26, 2012 by in Database, SQL Server

    In SQL Server 2005 and 2008, the OUTPUT clause makes your DML (INSERT/DELETE/UPDATE) statements return data back to you. In this article we will see examples of how OUTPUT clause can be used with INSERT, UPDATE, or DELETE to identify the actual rows affected by these statements.

    Get the last inserted record(s)

    Before OUTPUT clause, to get the last inserted row you could use @@IDENTITY if the table has IDENTITY column. Assume there is a table with UNIQUEIDENTIFIER as primary key, you could use OUTPUT clause as below to the get the latest row inserted

    CREATE TABLE [dbo].[PRODUCT_TBL](
    	[PRODUCT_ID] [uniqueidentifier] NOT NULL,
    	[NAME] [varchar](50) NULL,
    	[PRICE] [decimal](18, 2) NULL)
    
    INSERT INTO PRODUCT_TBL (PRODUCT_ID,NAME,PRICE)
    OUTPUT INSERTED.PRODUCT_ID,INSERTED.NAME,INSERTED.PRICE
    VALUES (NEWID(),'PRODUCT 1',29.80)
    
    

    Another advantage of OUTPUT clause is it can return multiple rows too, if you execute the query below

    INSERT INTO PRODUCT_TBL (PRODUCT_ID,NAME,PRICE)
    OUTPUT INSERTED.PRODUCT_ID,INSERTED.NAME,INSERTED.PRICE
    VALUES (NEWID(),'PRODUCT 2',31.80)
           ,(NEWID(),'PRODUCT 3',19.12)
           ,(NEWID(),'PRODUCT 4',26.45)
    
    

    Output of the above statement

    The OUTPUT clause also be used to get the data into a variable as shown below

    DECLARE @PRD_TBL table(
    	[PRODUCT_ID] [uniqueidentifier] NOT NULL,
    	[NAME] [varchar](50) NULL,
    	[PRICE] [decimal](18, 2) NULL)
    
    INSERT INTO PRODUCT_TBL (PRODUCT_ID,NAME,PRICE)
    OUTPUT INSERTED.*
    INTO @PRD_TBL
    VALUES (NEWID(),'PRODUCT 2',31.80),(NEWID(),'PRODUCT 3',19.12),(NEWID(),'PRODUCT 4',26.45)
    
    SELECT * FROM @PRD_TBL
    

    Using OUTPUT with Update Statement

    UPDATE PRODUCT_TBL
    SET PRICE = 33.64
    OUTPUT INSERTED.PRODUCT_ID,INSERTED.NAME,INSERTED.PRICE
    WHERE NAME = 'PRODUCT 2'
    
    

    Using OUTPUT with Delete Statement

    DELETE FROM PRODUCT_TBL
    OUTPUT DELETED.PRODUCT_ID,DELETED.NAME,DELETED.PRICE
    WHERE NAME = 'PRODUCT 2'
    
    Be Sociable, Share!
      Post Tagged with ,

    Written by

    Vanamali Juvvadi is a Web enthusiast and loves all things design and technology. Founded qnownow with a group of friends to share anything/everything they know/find on the internet.

    View all articles by

    Email : [email protected]

    Leave a Reply