?
Solved

the output into clause and table variable

Posted on 2011-03-18
3
Medium Priority
?
400 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 600 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 200 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 200 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

649 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