Improve Performance by using OUTPUT in Insert Or Update Statement in single query

Hello friends,

recently I needed to fire a single SQL statement for Insert and Update and using the single statement I also needed to return the result of all Inserted/Updated Records details..

after searching a lot ... I came to know a very interesting way to perform both statement in single Query.

Generally for the scenario I got lots of suggestions to use Stored Procedure, but which was difficult for my scenario.

so I must get solution for it.

ok ok... let's come to point.

let's we have created a sample Table using the query

Create Table Table_1(Id Int Identity,Name nvarchar(100))

to insert records and get its generated Id
basically we perform like this

Insert into Table_1(Name) 
Select 'Sandeep'
Select @@IDENTITY

and same way for update
here the Updated value comes from other process which is not available Directly
[My case is only For Insert. but the concept we can use in Update also]

Declare @NewValue nvarchar(100)='Sandeep Prajapati'
Declare @ID int=1

Update Table_1
set Name=@NewValue
Where Id=@ID

select @ID as ID,@NewValue as Name

Now We can use OUTPUT key word to select the inserted/Updated Value to get affected rows in single query which also helps to improve performance.

Here is query for Insert

Insert Into Table_1(Name)
OutPut Inserted.Id,Inserted.Name
values('Sandeep')

after execution of the query the output is

 

Same way for Update

Update Table_1
set Name='Sandeep Prajapati'
OutPut inserted.ID,inserted.Name

Hope this helps...

Happy Coding...