T-SQL Output Clause

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by bitbucket » T-SQL Output Clause

37 posts, Page 2 of 4

««1 2 3 4 »»»

T-SQL Output Clause

Rate Topic

Display Mode

Topic Options

Author

Message

core-909671

Posted Wednesday, January 06, 2010 5:59 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, December 02, 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

john.curran.z3g

Posted Wednesday, January 06, 2010 6:15 AM

SSC 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

Lynn Pettis

Posted Wednesday, January 06, 2010 6:36 AM

SSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:17 PM
Points: 21,832, Visits: 27,855

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.

Cool

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

Lynn Pettis

Posted Wednesday, January 06, 2010 6:39 AM

SSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:17 PM
Points: 21,832, Visits: 27,855

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.

Cool

Lynn Pettis