SQL Tips with OUTPUT options

 

This is a simple statement basically the Insert and delete.

When we insert or delete records from table objects, it shows numbers of rows affected. But we want to know how what the values of the row that is effected for Inset or delete statement.

As you know that there are 2 table named Inserted and deleted who take the information related to insert and delete, using this there is a simple steps that shows what's actually going on when insert and delete statements fires.

 

Step-1 [ Just Create the table objects ]

IF NOT EXISTS(SELECT * FROM sys.sysobjects WHERE xtype='U' AND name='TestTable')

   BEGIN

            CREATE TABLE TestTable

                               (Roll    DECIMAL(1)  NOT NULL IDENTITY(1,1) PRIMARY KEY,

                                Sname   VARCHAR(50) NOT NULL,

                                Class   DECIMAL(1)  NULL,

                                Section VARCHAR(1))

   END

GO

Step-2 [ Now fire some insert statement like this ]

-- Insert Statement with OUTPUT Options

INSERT INTO TestTable(Sname, Class, Section)   

OUTPUT inserted.roll, inserted.sname, inserted.Class, inserted.Section

VALUES ('Tufan', 5, 'A'),('Joydeep',1,'A'),('Palash',1,'A'),('Sangram',1,'A')

It will show the output of what it inserted not the numbers of row affected.

roll      sname             Class  Section

1          Tufan               1          A

2          Joydeep          1          A

3          Palash             1          A

4          Sangram         1          A

 

Step-3 Now fire the delete statement like this

DELETE TestTable  

OUTPUT deleted.roll, deleted.sname, deleted.Class, deleted.Section

WHERE Roll=1

It will show the output of what it deleted not the numbers of row affected.

 

roll      sname             Class  Section

1          Tufan               1          A

 

I think this article is quite informative and thanking you giving time on it.

 

Posted by: MR. JOYDEEP DAS