SQL: Return data by using OUTPUT clause in sql while insert.

Command categories:   DML (Data manipulation language)

Introduction:

The output clause allows you to return information from affected rows by an Insert, Update, or Delete statement. With
the help of OUTPUT clause you can gain confirmation that weather data has successfully inserted, updated, deleted or not.

Syntax of OUTPUT clause for insert statement:

Insert Into <table_name>
<column_name>

Output [inserted.<column_name>]
              or
              [inserted.<column_name> INTO <table_name>]
              or
              [<some function like getdate()> | <column_name> INTO <table_name>]

Values(<values corrospond to column name>)

For More clarification See Image:

 Example 1:

Insert Into employee
(firstname, lastname,title)

Output [inserted.firstname + '' +inserted.lastname as Full_name]

Values('Nishant', 'Kumar', 'Mr')

It will give output :
Full_Name
Nishant kumar

Image 3:

Example 2:

Insert Into employee
(firstname, lastname,title)

Output [inserted.firstname + '' +inserted.lastname INTO [modification_table]

Values('Nishant', 'Kumar', 'Mr')

It will give insert full name in modification_table
Select * From modification_table

Output:
full_name
Nishant Kumar

Here modification_table has only one attribute called full_name. 
If modification_table has more then one attribute like full_name 
and city and then you try to execute above sql statement then it 
will give error for mismatch column name so in that situation 
you have to modify statement

Modified statement:

Insert Into employee
(firstname, lastname,title)

      Output [inserted.firstname + '' +inserted.lastname 
      INTO [modification_table] (full_name)

Values('Nishant', 'Kumar', 'Mr')

I hope you understand OUTPUT clause concept in next post  I will describe OUTPUT clause for Delete that is very Interesting. If you find any problem regarding above feel free to comment your comment is always welcome.

 
Pgae Keyword: OUTPUT clause , Insert Output clause, OUTPUT CLAUSE in sql, Return value by OUTPUT clause in sql,
what is output clause in sql server, output keyword in sql, return data by output in sql server. use output clause in sql, how to use output clause in sql.

Advertisements