How to create a temp table similarly to creating a normal table?
Example:
CREATE TABLE table_name
(
column1 datatype,
column2 datatype,
column3 datatype,
....
);
3 Answers
Same thing, Just start the table name with # or ##:
CREATE TABLE #TemporaryTable -- Local temporary table - starts with single #
(
Col1 int,
Col2 varchar(10)
....
);
CREATE TABLE ##GlobalTemporaryTable -- Global temporary table - note it starts with ##.
(
Col1 int,
Col2 varchar(10)
....
);
Temporary table names start with # or ## - The first is a local temporary table and the last is a global temporary table.
Here is one of many articles describing the differences between them.
Comments
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
5 Comments
Gottfried Lesigang
The usage of session is not correct... A
@table is - like any variable - bound to one job. It will disappear with a GO and a called SP or function does not know it. A #table will live within one session, even in a called SP or function, while a ##table can be reached from different sessions.
Zohar Peled
Also, a table variable is not a temporary table. Read Martin Smith's post on DBO.StackExchange about it for details.
alroc
Table variables can often lead to poor execution plans due to their lack of indexes and statistics. They are scoped to the current batch, not "job" or session. While they have their uses, they are often not the best choice vs. a temp table.
Mark Kremers
A table variable is a temporary table, not a table created within the tempdb. However, even a @ table uses the tempdb (see blog.sqlauthority.com/2009/12/15/…)
ATC
A table variable is not the same as a temporary table. They are quite different. Also, a global temporary table (
##) can be accessed within different sessions and if I remember correctly, also by different logins.If you have an existing table with matching columns or a superset, you can also capture the types of the columns into a new temporary table called #temp_table simply by saying
select top 0 a, b, c into #temp_table from existing_table
Comments
#before table name