SQL: What is INSERT INTO #table?

Question

I wanted to know what does the # symbol mean? Why is it being used?

Example:

INSERT INTO #tmpContracts (sym, contractCount)
SELECT sym, 
       COUNT(DISTINCT(fulloptionsym)) AS contractCount

Answer 1

It means it is a temporary table. Take a look at the "Temporary Table" section of the CREATE TABLE documentation.

Answer 2

It is a temporary table, and is only available to the current session. It will be automatically dropped when it goes out of scope. If the same code is run by multiple people at the same time, each gets their own table, like local variables, and there is no need to worry about a collision.

Answer 3

A #Temp is a temp table that is active for the duration of the connection. It resides in the TempDB.

A #Temp is local (only available to the current connection) and is dropped once the connection is terminated.

A ##Temp is global (available to all connections) and is dropped once all connections using it are terminated.

corrected owing to feedback and correction from Gail

There is also a ## table type in SQL Server which is a temp table that is accessible removed when the server or the SQL Server service is restarted.

Answer 4

'#' is used for temporary table e.g : '#tmpContracts' '##' is used for global temporary table e.g : '##tmpContracts'

Read this temporary table

Answer 5

'#temp' = temperory table