Solved

the output into clause and table variable

Posted on 2011-03-18
3
393 Views
Last Modified: 2012-05-11
hi experts, i have this query
create table Reservation(
ID int identity (1,1) not null,
CustomerID int not null,
ArrivalDate datetime not null,
DepartDate datetime not null,
TotalAmount money not null,
DepositAmount as TotalAmount * .15);

declare @tblvar table
(
      ID int not null,
      CustomerID int not null,
      TotalAmount money not null
);
insert
into Reservation
      output inserted.ID, inserted.CustomerID, inserted.TotalAmount
into  @tblvar
      output inserted.*,
             GETDATE()
values(35, '11-10-08', '11-15-08', 400.00);

but i do not understand
1. the output into clause
2. the output clause
3.why it shows a single record?
0
Comment
Question by:enrique_aeo
3 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 150 total points
ID: 35164364
>but i do not understand
>1. the output into clause
that OUTPUT INTO clause saves the data into a table (variable), so you can proceed working on the data after the insert.
in this case, it's not really needed, visibly

>2. the output clause
the OUTPUT clause, without INTO, will just do what a SELECT would do with the inserted records.

>3.why it shows a single record?
because there is only 1 record inserted (VALUES calsue ...)


0
 
LVL 6

Assisted Solution

by:dan_mason
dan_mason earned 50 total points
ID: 35164706
The OUTPUT clause is explained in some more detail here:

http://technet.microsoft.com/en-us/library/ms177564.aspx
0
 
LVL 3

Assisted Solution

by:kumarnimavat
kumarnimavat earned 50 total points
ID: 35165306
SQL Server 2005 has new OUTPUT clause, which is quite useful. OUTPUT clause has accesses to inserted and deleted tables (virtual tables) just like triggers. OUTPUT clause can be used to return values to client clause. OUTPUT clause can be used with INSERT, UPDATE, or DELETE to identify the actual rows affected by these statements.

OUTPUT clause can generate table variable, a permanent table, or temporary table. Even though, @@Identity will still work in SQL Server 2005, however I find OUTPUT clause very easy and powerful to use. Let us understand OUTPUT clause using example.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

774 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question