Answer simple quiz at the end of the blog post and –
Every day one winner from India will get Joes 2 Pros Volume 2.
Every day one winner from United States will get Joes 2 Pros Volume 2.
Output
We will first begin our work with the OUTPUT clause, by diving into hands-on examples of deleting, inserting, and updating table data. Later, we will demonstrate logging these types of changes in a separate storage table. Note: The OUTPUT statement uses temporary INSERTED and/or DELETED tables. These memory-resident tables are used to determine the changes being caused by the INSERT, DELETE or UPDATE statements.
Delete Actions with Output
If you run the Reset script from chapter 14 of Book2 (SQLQueriesChapter14.0Setup.sql) then you see JProCo has five locations, two of them in Washington State. The DELETE query below will remove both the Seattle headquarters and the Spokane office.
SQL Server confirms that 2 rows were affected, but which 2 rows? It doesn’t say, and oftentimes you’re not interested in that detail. The OUTPUT clause displays the exact affected rows, even though they’re no longer present in your table.
Let’s check to confirm that all WA locations have been removed. All WA locations have been removed from the Location table.
Now reset the Location table to show all the original records. To do this rerun the current setup script (SQLQueriesChapter14.0Setup.sql)
Our DELETE query will again remove those same two records from the Location table. But first we will place an OUTPUT statement between the FROM and WHERE clauses. OUTPUT Deleted.* states that we want to see all deleted records and all of their fields in order. When we run this code, instead of saying “2 row(s) affected,” it will show us the actual deleted. The Deleted.* worked because the OUTPUT clause accessed the deleted memory-resident table.
Insert Actions with Output
The OUTPUT keyword exposed a special type of temporary table, or tables, based on the type of action you take. In our example, it created a table called Deleted to show us the records it removed. However, if we INSERT records, OUTPUT will not be able to create a Deleted table (see Figure 14.4). If you reattempt this query with an OUTPUT table called Inserted. This should display what is now the sixth record in the Location table. (see both figures below).
Update Actions with Output
We now know that the OUTPUT keyword will create an Inserted or a Deleted memory-resident table based on the query action you are running. The OUTPUT clause creates an Inserted table when you run an INSERT statement, and it creates the Deleted table when you run a DELETE operation. When you run an UPDATE statement, it creates both tables.
Let’s think about the UPDATE statement for a moment. DELETE and INSERT each perform a single action – namely, they either add or remove records.
However, an UPDATE statement is a little different. While it also performs a single action (replaces an existing value with a new value), reflecting that change with an OUTPUT statement is a little more complex. If your manager asked you to track the changes made with an UPDATE statement, you really would want to capture two things: 1) the existing record and 2) the updated record.
The OUTPUT clause handles this situation for you. When you run an UPDATE statement, the OUTPUT clause can create and populate both an Inserted and a Deleted table. This is a great way to see each old record next to the new record. Later we will see examples where the old and new records are entered side by side into the same storage table. Let’s see the UPDATE statement in action. First we’ll look just at Location 1, which is JProCo’s Seattle location as seen in the figure below.
Now let’s run an UPDATE to change the city to Kirkland.
Kirkland is now the city for Location 1. So if we were to use OUTPUT to generate the confirmation, would we want to see Seattle, or would we want to see Kirkland? As stated earlier, we can look at either one or both. We can see the record we just got rid of (i.e., the Seattle record), the record we just gained (i.e., the Kirkland record), or we can see both together.
Let’s change the city once more to Tacoma and then observe OUTPUT generating both Deleted and Inserted tables. The Figure below shows us both the old record and the new record.
Note: If you want to setup the sample JProCo database on your system you can watch this video. For this post you will want to run the SQLQueriesChapter14.0Setup.sql script from Volume 2.
Question 14
You have an HourlyPay table and are giving all hourly employees a $1 raise. When you run the update statement you want to see the EmpID, OldPay, and NewPay. What code will achieve this result?
- UPDATE HourlyPay SET Hourly = Hourly + 1
OUTPUT Deleted.EmpID , Deleted.Hourly as OldPay, Updated.Hourly as NewPay
WHERE Hourly IS NOT NULL - UPDATE HourlyPay SET Hourly = Hourly + 1
OUTPUT Deleted.EmpID , Updated.Hourly as OldPay, Deleted.Hourly as NewPay
WHERE Hourly IS NOT NULL - UPDATE HourlyPay SET Hourly = Hourly + 1
OUTPUT Deleted.EmpID , Inserted.Hourly as OldPay, Deleted.Hourly as NewPay
WHERE Hourly IS NOT NULL - UPDATE HourlyPay SET Hourly = Hourly + 1
OUTPUT Deleted.EmpID , Deleted.Hourly as OldPay, Inserted.Hourly as NewPay
WHERE Hourly IS NOT NULL
Rules:
Please leave your answer in comment section below with correct option, explanation and your country of resident.
Every day one winner will be announced from United States.
Every day one winner will be announced from India.
A valid answer must contain country of residence of answerer.
Please check my facebook page for winners name and correct answer.
Winner from United States will get Joes 2 Pros Volume 2.
Winner from India will get Joes 2 Pros Volume 2.
The contest is open till next blog post shows up at which is next day GTM+2.5.
Reference: Pinal Dave (https://blog.sqlauthority.com)
59 Comments. Leave new
The correct answer would be option 4.
UPDATE HourlyPay SET Hourly = Hourly + 1
OUTPUT Deleted.EmpID , Deleted.Hourly as OldPay, Inserted.Hourly as NewPay
WHERE Hourly IS NOT NULL
Options 1 and 2 reference a non-existent table (the “UPDATE” table). Option 3 is misleading as it would return the inverted values for OldPay and NewPay. Option 4 is correct.
Andre Soliz
USA
The correct answer is option 4.
UPDATE HourlyPay SET Hourly = Hourly + 1
OUTPUT Deleted.EmpID , Deleted.Hourly as OldPay, Inserted.Hourly as NewPay
WHERE Hourly IS NOT NULL
Here we want, empId which we are updating, so we can take this from inserted table or deleted table.
Old pay, first we delete old pay, we get this data from deleted table.
New pay,next we insert with new pay , we get this from inserted table.
bhasker
USA
Correct answer is 4
UPDATE HourlyPay SET Hourly = Hourly + 1
OUTPUT Deleted.EmpID , Deleted.Hourly as OldPay, Inserted.Hourly as NewPay
WHERE Hourly IS NOT NULL
as the deletion of records happens first before insertion we must look for oldpay in deleted table and newpay in insertion table.
USA
UPDATE HourlyPay SET Hourly = Hourly + 1
OUTPUT Deleted.EmpID , Inserted.Hourly as OldPay, Deleted.Hourly as NewPay
WHERE Hourly IS NOT NULL
Scalable- Systems
Manoj Sahoo
answer 4 is correct.
UPDATE HourlyPay SET Hourly = Hourly + 1
OUTPUT Deleted.EmpID , Deleted.Hourly as OldPay, Inserted.Hourly as NewPay
WHERE Hourly IS NOT NULL
Rajesh Garg
India
Answer 4
4.UPDATE HourlyPay SET Hourly = Hourly + 1
OUTPUT Deleted.EmpID , Deleted.Hourly as OldPay, Inserted.Hourly as NewPay
WHERE Hourly IS NOT NULL
Country : India
Answer 4
# UPDATE HourlyPay SET Hourly = Hourly + 1
OUTPUT Deleted.EmpID , Deleted.Hourly as OldPay, Inserted.Hourly as NewPay
WHERE Hourly IS NOT NULL
From India
Correct Option No. 4
UPDATE HourlyPay SET Hourly = Hourly + 1
OUTPUT Deleted.EmpID , Deleted.Hourly as OldPay, Inserted.Hourly as NewPay
WHERE Hourly IS NOT NULL
– Somnath Desai
India
Option 4 is the best answer
UPDATE HourlyPay SET Hourly = Hourly + 1
OUTPUT Deleted.EmpID , Deleted.Hourly as OldPay, Inserted.Hourly as NewPay
WHERE Hourly IS NOT NULL
Eric
USA
Option 4 is correct
4.UPDATE HourlyPay SET Hourly = Hourly + 1
OUTPUT Deleted.EmpID , Deleted.Hourly as OldPay, Inserted.Hourly as NewPay
WHERE Hourly IS NOT NULL
because deleted will give you old record and inserted will give you new record…
India
Option 4 is Correct.
Pakistan.
Hi Dave,
Can u clarify my doubt?
I want to insert the output values into another #result table , how can it possible.
or i want to assign the output values to a variable?
Please give me the query.
Example:
create table #tbl (id int identity(1,1) , name varchar(10) , dept varchar(10) )
insert into #tbl (name , dept )
output inserted.*
select ‘abhIShek Online4All’ , ‘d’
/*———————————————————-
Result:
id name dept
1 abhIShek Online4All d
———————————————————-
*/
———————————————————-
–i want to insert above result into #result table.
———————————————————-
Thanks in advance,
Regards,
abhIShek Online4all
Question 14
You have an HourlyPay table and are giving all hourly employees a $1 raise. When you run the update statement you want to see the EmpID, OldPay, and NewPay. What code will achieve this result?
Answer is 4.
UPDATE HourlyPay SET Hourly = Hourly + 1
OUTPUT Deleted.EmpID , Deleted.Hourly as OldPay, Inserted.Hourly as NewPay
WHERE Hourly IS NOT NULL
following is also Correct But Alias names are Wrong
3. UPDATE HourlyPay SET Hourly = Hourly + 1
OUTPUT Deleted.EmpID , Inserted.Hourly as OldPay, Deleted.Hourly as NewPay
WHERE Hourly IS NOT NULL
Following are Wrong
1. UPDATE HourlyPay SET Hourly = Hourly + 1
OUTPUT Deleted.EmpID , Deleted.Hourly as OldPay, Updated.Hourly as NewPay
WHERE Hourly IS NOT NULL
2. UPDATE HourlyPay SET Hourly = Hourly + 1
OUTPUT Deleted.EmpID , Updated.Hourly as OldPay, Deleted.Hourly as NewPay
WHERE Hourly IS NOT NULL
Option 4 is the correct answer.
4.UPDATE HourlyPay SET Hourly = Hourly + 1
OUTPUT Deleted.EmpID , Deleted.Hourly as OldPay, Inserted.Hourly as NewPay
WHERE Hourly IS NOT NULL
Very Very nice article. Thanks a lot
UPDATE HourlyPay SET Hourly = Hourly + 1
OUTPUT Deleted.EmpID , Deleted.Hourly as OldPay, Inserted.Hourly as NewPay
WHERE Hourly IS NOT NULL
cause update use two temporary table inserted and Deleted . In deleted it store old data and in inserted it store the new data .
1) UPDATE HourlyPay SET Hourly = Hourly + 1
OUTPUT Deleted.EmpID , Deleted.Hourly as OldPay, Updated.Hourly as NewPay
WHERE Hourly IS NOT NULL
UPDATE HourlyPay SET Hourly = Hourly + 1
OUTPUT Deleted.EmpID , Updated.Hourly as OldPay, Deleted.Hourly as NewPay
WHERE Hourly IS NOT NULL
UPDATE HourlyPay SET Hourly = Hourly + 1
OUTPUT Deleted.EmpID , Inserted.Hourly as OldPay, Deleted.Hourly as NewPay
WHERE Hourly IS NOT NULL
UPDATE HourlyPay SET Hourly = Hourly + 1
OUTPUT Deleted.EmpID , Deleted.Hourly as OldPay, Inserted.Hourly as NewPay
WHERE Hourly IS NOT NULL
1) UPDATE HourlyPay SET Hourly = Hourly + 1
OUTPUT Deleted.EmpID , Deleted.Hourly as OldPay, Updated.Hourly as NewPay WHERE Hourly IS NOT NULL;
*** WRONG: Because there is no ‘Updated’ virtual table.
2) UPDATE HourlyPay SET Hourly = Hourly + 1
OUTPUT Deleted.EmpID , Updated.Hourly as OldPay, Deleted.Hourly as NewPay WHERE Hourly IS NOT NULL;
*** WRONG: Because there is no ‘Updated’ virtual table.
3) UPDATE HourlyPay SET Hourly = Hourly + 1
OUTPUT Deleted.EmpID , Inserted.Hourly as OldPay, Deleted.Hourly as NewPay WHERE Hourly IS NOT NULL;
*** WRONG: Because Deleted.Hourly Should be the OLD value not NEW value.
4) UPDATE HourlyPay SET Hourly = Hourly + 1
OUTPUT Deleted.EmpID , Deleted.Hourly as OldPay, Inserted.Hourly as NewPay WHERE Hourly IS NOT NULL;
**** CORRECT answer.