{"id":151,"date":"2018-12-06T09:29:34","date_gmt":"2018-12-06T09:29:34","guid":{"rendered":"https:\/\/barkhane.com\/sql\/2018\/12\/06\/sql-temp-table\/"},"modified":"2024-03-03T18:01:55","modified_gmt":"2024-03-03T18:01:55","slug":"sql-temp-table","status":"publish","type":"post","link":"https:\/\/barkhane.com\/sql\/sql-temp-table\/","title":{"rendered":"SQL TEMP TABLE"},"content":{"rendered":"

The concept of temporary SQL TEMP TABLE is introduced by SQL server. It helps developers in many ways:<\/p>\n

Temporary tables<\/b>( SQL TEMP TABLE\u00a0)can be created at run-time and can do all kinds of operations that a normal table can do. These temporary tables are created inside tempdb database<\/a>.<\/p>\n

There are two types of temp tables based on the behavior and scope.<\/p>\n

    \n
  1. Local Temp Variable<\/li>\n
  2. Global Temp Variable<\/li>\n<\/ol>\n

    Local Temp Variable<\/h2>\n

    Local temp tables are only available at current connection time. It is automatically deleted when user disconnects from instances. It is started with hash (#) sign.<\/p>\n

    <\/p>\n

    CREATE<\/span>\u00a0TABLE<\/span>\u00a0#local<\/span>\u00a0temp<\/span>\u00a0table<\/span>\u00a0(\u00a0\u00a0\n\nUser<\/span>\u00a0id\u00a0int<\/span>,\u00a0\u00a0\n\nUsername\u00a0varchar<\/span>\u00a0(50),\u00a0\u00a0\n\nUser<\/span>\u00a0address\u00a0varchar<\/span>\u00a0(150)\u00a0\u00a0\n\n)<\/pre>\n
    <\/code>
    \n <\/p>\n
    Global Temp Variable<\/h2>\n
    Global temp tables name starts with double hash (##). Once this table is created, it is like a permanent table. It is always ready for all users and not deleted until the total connection is withdrawn.<\/p>\n
    <\/p>\n
    CREATE<\/span>\u00a0TABLE<\/span>\u00a0##new\u00a0global<\/span>\u00a0temp<\/span>\u00a0table<\/span>\u00a0(\u00a0\u00a0\n\nUser<\/span>\u00a0id\u00a0int<\/span>,\u00a0\u00a0\n\nUser<\/span>\u00a0name<\/span>\u00a0varchar<\/span>\u00a0(50),\u00a0\u00a0\n\nUser<\/span>\u00a0address\u00a0varchar<\/span>\u00a0(150)\u00a0\u00a0\n\n)\u00a0\u00a0\n\n<\/pre>\n
    <\/code>
    \n <\/p>\n","protected":false},"excerpt":{"rendered":"
    The concept of temporary SQL TEMP TABLE is introduced by SQL server. It helps developers in many ways: Temporary tables( […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","ast-disable-related-posts":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"categories":[2],"tags":[],"class_list":["post-151","post","type-post","status-publish","format-standard","hentry","category-sql"],"_links":{"self":[{"href":"https:\/\/barkhane.com\/sql\/wp-json\/wp\/v2\/posts\/151","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/barkhane.com\/sql\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/barkhane.com\/sql\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/barkhane.com\/sql\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/barkhane.com\/sql\/wp-json\/wp\/v2\/comments?post=151"}],"version-history":[{"count":0,"href":"https:\/\/barkhane.com\/sql\/wp-json\/wp\/v2\/posts\/151\/revisions"}],"wp:attachment":[{"href":"https:\/\/barkhane.com\/sql\/wp-json\/wp\/v2\/media?parent=151"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/barkhane.com\/sql\/wp-json\/wp\/v2\/categories?post=151"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/barkhane.com\/sql\/wp-json\/wp\/v2\/tags?post=151"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}