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, .... );

3

2 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.

0

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

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy