{"id":1164,"date":"2011-11-02T21:21:52","date_gmt":"2011-11-02T21:21:52","guid":{"rendered":"http:\/\/craftydba.com\/?p=1164"},"modified":"2011-11-16T01:46:10","modified_gmt":"2011-11-16T01:46:10","slug":"temporary-tables-part-2","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=1164","title":{"rendered":"Temporary Tables – Part 2"},"content":{"rendered":"

Today, I am going to talk about local and global temporary tables<\/a>. <\/p>\n

A local temporary table is defined by using the CREATE TABLE <\/a>syntax with the name of the table prefixed by one number sign #. The scope of the local temporary table is the current session or SPID. <\/p>\n

A global temporary table is defined by using the CREATE TABLE syntax with the name of the table prefixed by two number signs ##. The table is visible to all sessions. The scope of the global temporary table is the current session or the last session that dereferences the table.<\/p>\n

Both local and global tables are stored in the tempdb database. Since multiple process might be calling a stored procedure with a local temporary table, SQL Server post fixes the name of the table with a unique tag to keep them unique.<\/p>\n

I am going to use the car ownership table for person A to demonstration how to work with local temporary tables. Please see the tiny AUTOS database I created in earlier article<\/a>. <\/p>\n

The code snipet below creates a local temporary table with scope related to my SSMS query window.<\/p>\n

-- Database selection does not matter\r\nUSE model;\r\nGO\r\n\r\n-- Create local temp table\r\nCREATE TABLE #A\r\n(\r\n  MyId INT PRIMARY KEY CLUSTERED,\r\n  MyValue VARCHAR(20)\r\n)\r\nGO<\/span><\/pre>\n<\/p>\n
I am going to use an INSERT <\/a>statment to populate the table with sample data.<\/p>\n
-- Add data to local temp table\r\nINSERT INTO #A (MyId, MyValue) VALUES\r\n  (1, 'Continental'),\r\n  (2, 'Edsel'),\r\n  (3, 'Lincoln'),\r\n  (4, 'Mercury'),\r\n  (5, 'Ram')\r\nGO<\/span><\/pre>\n<\/p>\n
To really make sure the data is there, I am going to use a SELECT <\/a>statement to view the contents of the table.<\/p>\n
-- Show data from table A\r\nSELECT MyId, MyValue\r\nFROM #A\r\nGO\r\n<\/span><\/pre>\n<\/p>\n
Temporary tables would not be very useful if we can not perform UPDATE <\/a>and DELETE <\/a>operations on the records.  The code below records the third record from the table, updates the 5 record with new data, and shows the results.<\/p>\n
-- Remove record 3\r\nDELETE FROM #A WHERE MyId = 3;\r\nGO\r\n\r\n-- Update record 5\r\nUPDATE #A\r\nSET MyValue = 'Dodge Ram'\r\nWHERE MyId = 5;\r\nGO\r\n\r\n-- Show up dated data from table A\r\nSELECT MyId, MyValue\r\nFROM #A\r\nGO<\/span><\/pre>\n<\/p>\n
Last but not least, it is just good practice to clean up after yourself.  The code below looks for the
\ntable in tempdb.  If it exists, it drops the table.  <\/p>\n
-- Table names are stored with system post fix at end\r\nIF EXISTS (SELECT * FROM tempdb.sys.objects T WHERE T.TYPE = 'U' AND T.name LIKE '#A%')\r\n  DROP TABLE #A\r\nGO<\/span><\/pre>\n<\/p>\n
I will be using the Adventure Works<\/a> Sample database supplied by Microsoft during the rest of this talk.<\/p>\n
A common query pattern is to get the most recent id by date.  Joining these results to the original table
\nwill bring back the most recent record by date.<\/p>\n
The SELECT query below stores the last sales order by customer id as a local temporary table called #MC.  The main query joins to this table to return customer name, sales id, sub-total and order date.  This might be a query for the SALES team to cold call customers that have not ordered in the past x months.<\/p>\n
-- Use the A.W. database\r\nUSE AdventureWorks2008R2\r\nGO\r\n\r\n-- Local temp table (MC) - last sales order by customer in 2008 \r\nSELECT \r\n  CustomerID AS MyCustomer,\r\n  MAX(OrderDate) AS MyDate\r\nINTO\r\n  #MC\r\nFROM \r\n  Sales.SalesOrderHeader\r\nWHERE\r\n  YEAR(OrderDate) = 2008 \r\nGROUP BY \r\n  CustomerID\r\n\r\n-- Get customer name, sales id, sub-total and order date\r\nSELECT \r\n  P.FirstName, \r\n  P.LastName,\r\n  SH.SalesOrderID, \r\n  SH.SubTotal,\r\n  SH.OrderDate\r\nFROM\r\n  Sales.SalesOrderHeader AS SH   \r\n  INNER JOIN #MC ON SH.CustomerID = MC.MyCustomer AND SH.OrderDate = MC.MyDate\r\n  INNER JOIN Sales.Customer C ON MC.MyCustomer = C.CustomerID\r\n  INNER JOIN Person.Person P ON C.PersonID = P.BusinessEntityID\r\nORDER BY \r\n  SH.OrderDate ASC  \r\n\r\n\r\n-- Table names are stored with system post fix at end\r\nIF EXISTS (SELECT * FROM tempdb.sys.objects T WHERE T.TYPE = 'U' AND T.name LIKE '#MC%')\r\n  DROP TABLE #MC\r\nGO<\/span><\/pre>\n<\/p>\n
The code snipets above that drop tables have one fatal bug.  If the code is executed from two query windows at the same time, one window will fail since it will try to drop a table that does not exist.  One fix to this bug is to add the SPID to the end of the table name.  Since each session (SPID) is now unique at a given time, the correct result will come back from the EXISTS <\/a>query.<\/p>\n
In short, local and global temporary tables have been around for a long time.  Since older versions of the SQL Server engine do not support constructs such as table variables or common table expressions, it is wise to know how to use this technique.  Next time, I will talk about table variables.<\/p>\n","protected":false},"excerpt":{"rendered":"
Today, I am going to talk about local and global temporary tables. A local temporary table is defined by using the CREATE TABLE syntax with the name of the table prefixed by one number sign #. The scope of the local temporary table is the current session or SPID. A global temporary table is defined by using the CREATE TABLE syntax with the name of the table prefixed by two number signs ##. The table is visible to all sessions. The scope of the global temporary table is the current…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9],"tags":[245,246,63,33,108,12,184,30,15,183,37,28,29,32],"class_list":["post-1164","post","type-post","status-publish","format-standard","hentry","category-db-dev","tag-245","tag-246","tag-create-table","tag-delete","tag-exists","tag-free-code","tag-global-temporary-tables","tag-insert","tag-john-f-miner-iii","tag-local-temporary-tables","tag-select","tag-sql-server","tag-tsql","tag-update"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/1164","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1164"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/1164\/revisions"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1164"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1164"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1164"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}