SW Skeen (5/7/2014)
I had a submittal this morning for release to production that contains 2 aliases 'o' for a physical table and a temp table. To my knowledge I haven't seen this before and am wondering what is likely or guaranteed to happen if this code were released. The procedure compiles fine so the syntax doesn't appear to cause an issue.The procedure code snippet here is the representative example:
SELECT 1,2,3,4 from ORDERHEADER O
CREATE TABLE #OUTPUT (1,2,3,4)
INSERT INTO OUTPUT (1,2,3,4)
UPDATE O SET ord_hdrnumber = 5 ---WHAT HAPPENS HERE!
The alias you used in a previous query has no bearing on the subsequent update statement. What would happen with that update is it would attempt to update the persistent object named O. This could be a table, view or possibly a database alias (not to be confused with the query level alias at the beginning of your snippet). If there is no persistent object with that name it will throw and error about object not found.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/