Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

T-SQL Output Clause Expand / Collapse
Author
Message
Posted Wednesday, January 6, 2010 5:59 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, December 2, 2010 8:45 AM
Points: 14, Visits: 116
The temporary table does not contain a column "CategoryName". It attempts to update a column that does not exist. It would throw an error, not return rows.
Post #842707
Posted Wednesday, January 6, 2010 6:15 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, February 18, 2013 7:25 AM
Points: 236, Visits: 53
IF you have Northwind installed, the statement will update a Northwind Categories table and use the values from the inserted/deleted OUTPUT option to insert into the temp table.
Post #842712
Posted Wednesday, January 6, 2010 6:36 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:58 PM
Points: 20,705, Visits: 32,356
Rich Holt (1/6/2010)
I should also caution you about the other way to look at this:

If you insert/update/delete TABLE variable rows inside a transaction, and subsequently rollback the work, the table variable changes are not rolled back with the transaction. This could lead to data inconsistencies.
Be careful.


I agree, be careful; however, there may be a reason that you want the output from the insert/update/delete regardless of the success/failure of the transactions. If this is the case, then using table variables is required.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #842734
Posted Wednesday, January 6, 2010 6:39 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:58 PM
Points: 20,705, Visits: 32,356
mjohnson-909671 (1/6/2010)
The temporary table does not contain a column "CategoryName". It attempts to update a column that does not exist. It would throw an error, not return rows.


Take a closer look at the code. CategoryName is being updated in the Categories table. The OUTPUT clause is inserting the Old and New Category names into a temporary table as an audit of changes.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #842736
Posted Wednesday, January 6, 2010 6:41 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, December 2, 2010 8:45 AM
Points: 14, Visits: 116
Ah. Ok. I don't have Northwind installed anywhere.
Post #842738
Posted Wednesday, January 6, 2010 6:45 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:58 PM
Points: 20,705, Visits: 32,356
Neither do I.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #842741
Posted Wednesday, January 6, 2010 7:07 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 9:52 AM
Points: 519, Visits: 2,822
Where was the choice for "Could not locate entry in sysdatabases for database 'Northwind'."?


Post #842762
Posted Wednesday, January 6, 2010 7:23 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:58 PM
Points: 20,705, Visits: 32,356
dmbaker (1/6/2010)
Where was the choice for "Could not locate entry in sysdatabases for database 'Northwind'."?


Actually irrelevant to answering the question.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #842786
Posted Wednesday, January 6, 2010 7:29 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 9:52 AM
Points: 519, Visits: 2,822
I know, I got the question right. Just being snarky.


Post #842793
Posted Wednesday, January 6, 2010 7:39 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, December 2, 2010 8:45 AM
Points: 14, Visits: 116
Gee.thanks. First time I ever post... thanks a ton. You're the best!
Post #842803
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse