Output cluase – sql

Returns information from, or expressions based on, each row affected by an INSERT, UPDATE, DELETE, or MERGE statement. These results can be returned to the processing application for use in such things as confirmation messages, archiving, and other such application requirements. The results can also be inserted into a table or table variable. Additionally, you can capture the results of an OUTPUT clause in a nested INSERT, UPDATE, DELETE, or MERGE statement, and insert those results into a target table or view.

select recently added record ID

–using outpu clause
declare @t as table (id int,name nvarchar(50),salary int)

insert into mydata..Mydata
output inserted.id,inserted.name,inserted.salary into @t(id,name,salary)
values (77,’name’,550000)
select max(id) from @t

–using SCOPE_IDENTITY() /@@IDENTITY
insert into [mydata].[dbo].[EmpData] values (‘NAME’,’2011-02-02′,’ADDRESS’)
select SCOPE_IDENTITY()

—  select @@IDENTITY

–using normal query

declare @id int = 7, @name nvarchar(50) = ‘OLP’ , @salary int = 34999
INSERT INTO mydata..Mydata values(@id,@name,@salary)
select @id
select * from mydata..Mydata

 

 

–Retrieve the most recent record from a database

SELECT TOP 1 *
FROM [mydata].[dbo].[EmpData]
ORDER BY id DESC

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s