sql-serverstored-procedurestemp-tables

How to create temp table using Create statement in SQL Server?


How to create a temp table similarly to creating a normal table?

Example:

CREATE TABLE table_name 
(
    column1 datatype,
    column2 datatype,
    column3 datatype,
     ....
 );

Solution

  • A temporary table can have 3 kinds, the # is the most used. This is a temp table that only exists in the current session. An equivalent of this is @, a declared table variable. This has a little less "functions" (like indexes etc) and is also only used for the current session. The ## is one that is the same as the #, however, the scope is wider, so you can use it within the same session, within other stored procedures.

    You can create a temp table in various ways:

    declare @table table (id int)
    create table #table (id int)
    create table ##table (id int)
    select * into #table from xyz