Problem
Is it required to use # before creating a SQL Server temporary table?
Example:
SELECT column1, column2, someInt, someVarChar
INTO ItemBack1
FROM table2
WHERE table2.ID = 7
Is it necessary to use the # sign for ItemBack1?
If not, what is the point of using # to create temp tables?
Asked by Arun CM
Solution #1
Yes. To create temporary tables, prefix the table name with “#” (hash).
Create the table now if you will not need it later. Temporary tables are similar to regular tables. It is, however, created in tempdb. Furthermore, it can only be accessed within the current session, i.e. For example, if another user tries to access the temp table you established, he will be denied access.
“##” (double-hash produces a “Global” temp table that other sessions can access as well.
Basics of Temporary Tables may be found in http://www.codeproject.com/Articles/42553/Quick-Overview-Temporary-Tables-in-SQL-Server-2005/.
Consider utilizing Table Variables if your table has fewer than 5000 rows and does not contain data types like nvarchar(MAX) or varbinary(MAX).
They are the fastest since they are stored in RAM just like any other variable. They are also kept in tempdb rather than RAM.
DECLARE @ItemBack1 TABLE
(
column1 int,
column2 int,
someInt int,
someVarChar nvarchar(50)
);
INSERT INTO @ItemBack1
SELECT column1,
column2,
someInt,
someVarChar
FROM table2
WHERE table2.ID = 7;
http://odetocode.com/articles/365.aspx for further information about Table Variables
Answered by mobiledaemon
Solution #2
The distinction between ItemBack1 and #ItemBack1 is that the first is persistent (permanent), while the second is transitory.
Take a look at your question once more.
Yes, because the table will not be a temporary table without this preceding #, and it will be independent of all sessions and scopes.
Answered by zzlalani
Post is based on https://stackoverflow.com/questions/16749045/is-it-necessary-to-use-for-creating-temp-tables-in-sql-server