SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Output Clause in Simple Examples – Day 14 of 35

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?

  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
  3. UPDATE HourlyPay SET Hourly = Hourly + 1
    OUTPUT Deleted.EmpID , Inserted.Hourly as OldPay, Deleted.Hourly as NewPay
    WHERE Hourly IS NOT NULL
  4. 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 http://blog.sqlauthority.com which is next day GTM+2.5.

Reference:  Pinal Dave (http://blog.SQLAuthority.com)

About these ads

64 thoughts on “SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Output Clause in Simple Examples – Day 14 of 35

  1. Correct answer is N0. 4, because the OUTPUT statement uses temporary INSERTED and/or DELETED tables. Thus, answers 1 and 2 are incorrect because they use a non-existent table called UPDATED, and answer 3 presents the Hourly fields with the incorrect order: OldPay must be in the DELETED table and NewPay must be in the INSERTED table. Answer 4 shows the Hourly fields with the correct order.
    Rene Castro
    El Salvador

  2. 4) UPDATE HourlyPay SET Hourly = Hourly + 1
    OUTPUT Deleted.EmpID , Deleted.Hourly as OldPay, Inserted.Hourly as NewPay
    WHERE Hourly IS NOT NULL

    1) and 2) are wrong because there is no column prefix called Updated; only INSERTED and DELETED
    3) will show old hourly value as new and the old one as new

    Leo Pius
    USA

  3. Number 4 would be correct. The Employee ID and the Old Pay would be from the Deleted table and the New Pay would have to come from the Inserted table. Number 3 would be close except that the pays are switched. Since there is no updated table, 1 and 2 would never work.

    Jason
    Georgia – USA

  4. Pingback: SQL SERVER – Win a Book a Day – Contest Rules – Day 0 of 35 Journey to SQLAuthority

  5. 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

  6. Correct answer is #4. In first two answers there is a reference to non-existing UPDATED table (there is no such table) and in the #3 the inserted and deleted tables used in wrong orders.

    So, only #4 correctly represents the desired output

    I am from USA

  7. ans is 4:
    4.UPDATE HourlyPay SET Hourly = Hourly + 1
    OUTPUT Deleted.EmpID , Deleted.Hourly as OldPay, Inserted.Hourly as NewPay
    WHERE Hourly IS NOT NULL
    Because update use two temporary table inserted and Deleted . In deleted it store old data and in inserted it store the new data . So only option 4 is correct.

    http://msdn.microsoft.com/en-us/library/ms177564.aspx

    Partha
    India,

  8. Correct answer is 4 as

    UPDATE HourlyPay SET Hourly = Hourly + 1
    OUTPUT Deleted.EmpID , Deleted.Hourly as OldPay, Inserted.Hourly as NewPay
    WHERE Hourly IS NOT NULL

    1 and 2 and wrong because there used wrong table updated

    3 is wrong because of

    Deleted.Hourly as NewPay
    Inserted.Hourly as OldPay

    Varinder sandhu (India)

  9. Hi,

    Correct Ans is 4
    UPDATE HourlyPay SET Hourly = Hourly + 1
    OUTPUT Deleted.EmpID , Deleted.Hourly as OldPay, Inserted.Hourly as NewPay
    WHERE Hourly IS NOT NULL

    Output Deleted.EmpID, Deleted.Hourly as OldPay Display Employee ID and Hourly Pay of Before Update

    Output Inserted.Hourly Hourly Pay after Update.

    I am from India.

  10. Hi, Answer 1,2 are incorrect because it has Updated while should have Deleted/Inserted.
    3 incorrect because it has ” OUTPUT Deleted.EmpID , Inserted.Hourly as OldPay, Deleted.Hourly as NewPay
    WHERE Hourly IS NOT NULL” Mixed of deleted/inserted
    Only 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”
    It fulfill all conditions.

    Thanks….
    Rajneesh Verma
    (INDIA)

  11. Option 4 would be the right query for getting the appropriate output i.e EmpID, OldPay, and NewPay.

    UPDATE HourlyPay SET Hourly = Hourly + 1
    OUTPUT Deleted.EmpID , Deleted.Hourly as OldPay, Inserted.Hourly as NewPay
    WHERE Hourly IS NOT NULL

    As we are Updating the HourlyPay for all Employee’s [EmpID], By executing the above query
    the output will be the Employee ID & Old Pay [Deleted one] & New Pay [Inserted One]

    Option 3 would result in the Old Pay and New Pay value interchanged. Which we do not want.
    Option 2 & 1 would not work

    -Mukesh (India)

  12. Hi Sir,

    The correct option is 4th Query.

    Option 1 and 2 are incorrect query because it is referencing an “Updated” memory-resident table which is invalid.

    Option 3.UPDATE HourlyPay SET Hourly = Hourly + 1
    OUTPUT Deleted.EmpID , Inserted.Hourly as OldPay, Deleted.Hourly as NewPay
    WHERE Hourly IS NOT NULL

    The query is proper but the referencing for OldPay and NewPay are interchanged.

    Option 4.UPDATE HourlyPay SET Hourly = Hourly + 1
    OUTPUT Deleted.EmpID , Deleted.Hourly as OldPay, Inserted.Hourly as NewPay
    WHERE Hourly IS NOT NULL

    Is the correct answer as Deleted.Hourly is the OldPay and Inserted.Hourly is the NewPay which was updated.

    P.Anish Shenoy
    INDIA,Bangalore,Karnataka

  13. 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

    Because old pay come from deleted table record and new record from inserted table record.

    Thank you
    Country:India

  14. Correct Answer: #4
    Reason: The DELETED table would give us information about the OldPay, while the INSERTED table would give us information about the NewPay. The EmpID is the key, and in an update operation such as this, it does not make a difference whether we fetch it from the DELETED or from the INSERTED tables.

    Country of residence: India

  15. Option 4 is correct because option 1 and 2 are referring to UPDATED which doesn’t exist. Option 3 is wrong because Inserted will have NewPay and its used as Old Pay.

    So Option 4 is correct.

    Regards,

    Sudhir Chawla, New Delhi (INDIA)

  16. Correct Option is 4.

    4.UPDATE HourlyPay SET Hourly = Hourly + 1
    OUTPUT Deleted.EmpID , Deleted.Hourly as OldPay, Inserted.Hourly as NewPay
    WHERE Hourly IS NOT NULL
    This will produce the desire result.
    (Sale, Nigeria)

  17. Correct Answer is Option 4

    Explanation: As you had explained that whenever any row or value is deleted form table, it is stored in ‘deleted’ table and on inserting any row or value the value is also stored in ‘inserted’ table. In case of Update, old value is first deleted and then new value is inserted. So, in this case old pay can be known from deleted table and new pay can be known from inserted table.Hence, Option 4 is correct as per the question.

    UPDATE HourlyPay SET Hourly = Hourly + 1
    OUTPUT Deleted.EmpID , Deleted.Hourly as OldPay, Inserted.Hourly as NewPay
    WHERE Hourly IS NOT NULL

    Country – INDIA (Gujarat)

  18. Correct Answer is:4

    Option 1 & 2 are not correct as we don’t have any logical table named updated.
    Option 3 is not correct, because in case of oldpay we are using inserted logical table which gives new pay and incase of newpay we are using deleted logical table which will give the old value i.e. Value prior to update. So the correct answer is option 4.

    Thanks,
    Basavaraj Biradar
    India

  19. 1.UPDATE HourlyPay SET Hourly = Hourly + 1
    OUTPUT Deleted.EmpID , Deleted.Hourly as OldPay, Updated.Hourly as NewPay
    WHERE Hourly IS NOT NULL

    – No OUTPUT table with name Updated.

    2.UPDATE HourlyPay SET Hourly = Hourly + 1
    OUTPUT Deleted.EmpID , Updated.Hourly as OldPay, Deleted.Hourly as NewPay
    WHERE Hourly IS NOT NULL

    – No OUTPUT table with name Updated.

    3.UPDATE HourlyPay SET Hourly = Hourly + 1
    OUTPUT Deleted.EmpID , Inserted.Hourly as OldPay, Deleted.Hourly as NewPay
    WHERE Hourly IS NOT NULL

    – Returns wrong value. Oldpay displays new value and NewPay displays old 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.

    Thanks

    Shree

    Bangalore India

  20. The right option is 4. Reason:

    1. There is no “UPDATED” magic table – Hence, options 1 & 2 are invalid
    2. When you update, the incremented value will reside in INSERTED, and the old value, in DELETED. In Query 3, this is inverted. Hence, it would give a logically incorrect output.

    Ramakrishnan RS
    Mysore, India

  21. Correct Option Is Option 4 i.e

    UPDATE HourlyPay SET Hourly = Hourly + 1
    OUTPUT Deleted.EmpID , Deleted.Hourly as OldPay, Inserted.Hourly as NewPay
    WHERE Hourly IS NOT NULL

    Because we want OldPay and NewPay of Employee.And Output Clause Perform with “Deleted” in Update/Delete Query and “Inserted” in Update/Insert Query.And the right query is mention in Option 4 where we are using Deleted.Hourly to get Old value that is replaced, and Inserted.Hourly for new value that is replaced value.

    Alok Chandra Shahi
    India

  22. HI Pinal Sir,

    The Correct Option for the above question 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

    Explanation :

    Given : We have an HourlyPay table and are giving all hourly employees a $1 raise.

    The requirement is When we run the update statement we want to see the EmpID, OldPay, and NewPay.

    The New Pay will be in Inserted Output table because It is the new value and gets updated when we update or Inserted so to get the required Fields : 1) EmpId is common so any Output will fetch the required value, Old Pay will be deleted and Inserted with a new value so we have Deleted Output table hence Deleted.hourly is correct Option.

    The Only Option that has this combination is Option 4.

    Why Other Options are wrong:

    Option 1) UPDATE HourlyPay SET Hourly = Hourly + 1
    OUTPUT Deleted.EmpID , Deleted.Hourly as OldPay, Updated.Hourly as NewPay
    WHERE Hourly IS NOT NULL

    Answer) There is nothing called updated Output table in SQL SERVER so Option Ruled Out.

    Option 2) UPDATE HourlyPay SET Hourly = Hourly + 1

    OUTPUT Deleted.EmpID , Updated.Hourly as OldPay, Deleted.Hourly as NewPay
    WHERE Hourly IS NOT NULL

    Answer) There is nothing called updated Output table in SQL SERVER so Option Ruled Out.

    Option 3) UPDATE HourlyPay SET Hourly = Hourly + 1
    OUTPUT Deleted.EmpID , Inserted.Hourly as OldPay, Deleted.Hourly as NewPay
    WHERE Hourly IS NOT NULL

    Answer) The Abouve query gives Output that’s all right but the New Pay and Old Pay values are interchanged so definitely a very wrong Output.

    Hence the Correct Option is 4 th optiom.

    and a very happy Advance Independece Day to you and all my country people!!!!

    DILIP KUMAR JENA
    Country : INDIA

  23. 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;

    Abobve query displays Employee ID and the Old Pay from the Deleted table and the New Pay from the Inserted table.

    Gopalakrishnan Arthanarisamy
    Unisys, Bangalore, India

  24. #4.

    Deleted and Inserted are the correct temp tables to Output on to show the records before and after the update for comparison

  25. Answer Option : 4

    UPDATE HourlyPay SET Hourly = Hourly + 1
    OUTPUT Deleted.EmpID , Deleted.Hourly as OldPay, Inserted.Hourly as NewPay
    WHERE Hourly IS NOT NULL

    Chennai, TamilNadu, India

  26. Hi Pinal,
    The correct option is 4.
    Explanation- A per the blog Update action+output will give Insert+Delete tables
    Now as the first 2 options does not contain Insert so they are opted out.
    The requirement is that when Update statement is run we can see the EmpID, OldPay, and NewPay. that is previous pay(Deleted.Hourly) and the new pay(Inserted.Hourly) of the employee which can only be achieved from the below update statement
    UPDATE HourlyPay SET Hourly = Hourly + 1
    OUTPUT Deleted.EmpID , Deleted.Hourly as OldPay, Inserted.Hourly as NewPay
    WHERE Hourly IS NOT NULL.
    This cannot be achieved using option 3 as pay of the employee
    before updation will not get displayed under OldPay and pay of the employee after updation will not get displayed under NewPay.

    Thanks,
    Manik Dey
    Country :- India

  27. Option 4 will yeild the desired results:
    4.UPDATE HourlyPay SET Hourly = Hourly + 1
    OUTPUT Deleted.EmpID , Deleted.Hourly as OldPay, Inserted.Hourly as NewPay
    WHERE Hourly IS NOT NULL

    Country: United States

  28. UPDATE HourlyPay SET Hourly = Hourly + 1
    OUTPUT Deleted.EmpID , Deleted.Hourly as OldPay, Inserted.Hourly as NewPay
    WHERE Hourly IS NOT NULL

    Chetan
    Country: USA

  29. Hi Pinal,

    Challenge:
    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?

    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
    3.UPDATE HourlyPay SET Hourly = Hourly + 1
    OUTPUT Deleted.EmpID , Inserted.Hourly as OldPay, Deleted.Hourly as NewPay
    WHERE Hourly IS NOT NULL
    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:
    The 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

    Explanation:
    Choice #1 won’t compile because of the phrase “Updated.Hourly as NewPay” in the OUTPUT clause. It should be “Inserted.Hourly as NewPay”. There is no “Updated” table to query against.

    Choice #2 also won’t compile due to the phrase “OUTPUT Deleted.EmpID , Updated.Hourly as OldPay, Deleted.Hourly as NewPay”. It should be “OUTPUT Deleted.EmpID , Deleted.Hourly as OldPay, Inserted.Hourly as NewPay”. Here also, there is no “Updated” table to query against.

    Choice #3 will compile, but will show the results as EmpID, new hourly rate as OldPay and old hourly rate as NewPay. The pay rate labels are inversed and incorrect.

    Choice #4 is the correct SQL to show the EmpID, the old hourly rate as OldPay and the new hourly rate as NewPay.

    Country:
    United States

    Thanks for the knowledge!

    Regards,

    Bill Pepping

  30. 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

    Since you are looking for the old pay you want the Deleted.Hourly table for that one and the Inserted.Hourly for the new pay. The other queries have this incorrect or are incorrectly looking fora field in an Updated table.

  31. This blog 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

    Chennai, India

  32. 4)

    UPDATE HourlyPay SET Hourly = Hourly + 1
    OUTPUT Deleted.EmpID , Deleted.Hourly as OldPay, Inserted.Hourly as NewPay
    WHERE Hourly IS NOT NULL

  33. 4)

    UPDATE HourlyPay SET Hourly = Hourly + 1
    OUTPUT Deleted.EmpID , Deleted.Hourly as OldPay, Inserted.Hourly as NewPay
    WHERE Hourly IS NOT NULL

    Location : India

  34. 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

  35. 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

  36. 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

  37. 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

  38. 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

  39. Q 16) SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – System and Time Data Types – Day 16 of 35

    A.) Since Updated is not one of the temporary tables created by the UPDATE statement (1) and (2) are both incorrect answers. Because Inserted.Hourly will contain the new value that was just inserted by the UPDATE statement and Deleted.Hourly will contain the old value that was just deleted (3) will not be correct either. Since the code in (4) is referring to the Deleted.Hourly field as the OldPay and the Inserted.Hourly field as the NewPay it is the correct answer.

    Winner from USA: Bill Pepping

    Winner from India: Gopalakrishnan Arthanarisamy

    I thank you all for participating here. The permanent record of this update is posted on facebook page.

  40. 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

  41. 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

  42. 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

  43. 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

  44. 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

  45. 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

  46. 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

  47. Pingback: SQL SERVER – SQL Query Techniques For Microsoft SQL Server 2008 – Book Available for SQL Server Certification Journey to SQLAuthority

  48. 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 .

  49. Pingback: SQL SERVER – A Quick Look at Logging and Ideas around Logging « SQL Server Journey with SQL Authority

  50. Pingback: SQL SERVER – Identity Fields – Contest Win Joes 2 Pros Combo (USD 198) – Day 2 of 5 « SQL Server Journey with SQL Authority

  51. 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

  52. 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.

  53. Pingback: SQL SERVER – Weekly Series – Memory Lane – #042 | Journey to SQL Authority with Pinal Dave

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