{"id":251,"date":"2023-11-21T17:23:45","date_gmt":"2023-11-21T16:23:45","guid":{"rendered":"https:\/\/bitwise.exposed\/?p=251"},"modified":"2026-03-16T09:38:48","modified_gmt":"2026-03-16T08:38:48","slug":"create-temptable-in-t-sql-with-select-into","status":"publish","type":"post","link":"https:\/\/bitwise.exposed\/index.php\/2023\/11\/21\/create-temptable-in-t-sql-with-select-into\/","title":{"rendered":"Create #TempTable with T-SQL"},"content":{"rendered":"\n

Temporary tables in SQL are used to store and process intermediate results within a session. They are particularly useful when you need to store temporary data that you want to manipulate or join with other tables. <\/p>\n\n\n\n

Also for developing queries in complex and large databases, they could be really useful. For example, in a staging area where views build on each other, queries could be quite slow. Persist views in TempTables during a session could be a practical solution. Also for readability and what ever comes in mind. <\/p>\n\n\n\n

There’s more than one way in SQL to create a temporary table, but Microsoft SQL Server (T-SQL) offers a convenient way with the ‚INTO‘ statement to create a temporary table and populate it with the results of a query in a single statement. This is often referred to as a ’select into‘ operation.

Here’s an example using a local temporary table:<\/p>\n\n\n\n

SQL<\/span><\/path><\/path><\/svg><\/span>
-- Create and populate a local temporary table using SELECT INTO<\/span><\/span>\nSELECT<\/span> Column1, Column2<\/span><\/span>\nINTO<\/span> #TempTable<\/span><\/span>\nFROM<\/span> YourSourceTable<\/span><\/span>\nWHERE<\/span> SomeCondition;<\/span><\/span>\n<\/span>\n-- Query the temporary table<\/span><\/span>\nSELECT<\/span> * <\/span>FROM<\/span> #TempTable;<\/span><\/span>\n<\/span>\n-- Drop the temporary table (it will be dropped automatically when the session ends)<\/span><\/span>\nDROP<\/span> <\/span>TABLE<\/span> #TempTable;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
In this example:<\/p>\n\n\n\n
    \n
  • YourSourceTable<\/strong> is the source table from which you want to select data.<\/li>\n\n\n\n
  • SomeCondition<\/strong> is a condition that filters the data you want to insert into the temporary table.<\/li>\n<\/ul>\n\n\n\n
    The ‚SELECT INTO‘ <\/strong>statement creates the ‚#TempTable‘<\/strong> if it doesn’t exist and inserts the result set of the SELECT query into it.<\/p>\n\n\n\n
    If you want to create a global temporary table, you can use the ‚##‘<\/strong> prefix:<\/p>\n\n\n\n
    SQL<\/span><\/path><\/path><\/svg><\/span>
    -- Create and populate a global temporary table using SELECT INTO<\/span><\/span>\nSELECT<\/span> Column1, Column2<\/span><\/span>\nINTO<\/span> ##GlobalTempTable<\/span><\/span>\nFROM<\/span> YourSourceTable<\/span><\/span>\nWHERE<\/span> SomeCondition;<\/span><\/span>\n<\/span>\n-- Query the global temporary table<\/span><\/span>\nSELECT<\/span> * <\/span>FROM<\/span> ##GlobalTempTable;<\/span><\/span>\n<\/span>\n-- Drop the global temporary table (it will be dropped automatically when the session ends)<\/span><\/span>\nDROP<\/span> <\/span>TABLE<\/span> ##GlobalTempTable;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
    Note:<\/strong> The examples above refers to T-SQL used in Microsoft SQL Server.<\/p>\n","protected":false},"excerpt":{"rendered":"
    Microsoft SQL Server (T-SQL) offers a convenient way with the ‚INTO‘ statement to create a temporary table and populate it with the results of a query in a single statement.<\/p>\n","protected":false},"author":1,"featured_media":254,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[16,17],"tags":[],"class_list":["post-251","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-pattern","category-sql"],"_links":{"self":[{"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/posts\/251","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/comments?post=251"}],"version-history":[{"count":6,"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/posts\/251\/revisions"}],"predecessor-version":[{"id":274,"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/posts\/251\/revisions\/274"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/media\/254"}],"wp:attachment":[{"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/media?parent=251"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/categories?post=251"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/tags?post=251"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}