Temporary Tables

Temporary Tables

There are 2 types of temporary tables, local and global. Local temporary tables are created using a single pound (#) sign and are visible to a single connection and automatically dropped when that connection ends. Global temporary tables are created using a double pound (##) sign and are visible across multiple connections and users and are automatically dropped when all SQL sessions stop referencing the global temporary table.
How to Create:

CREATE TABLE #MyTempTable
(
 PolicyId INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
 LastName VARCHAR(50) NOT NULL 
) 

How to Use:

INSERT INTO #MyTempTable
SELECT PolicyId, LastName 
FROM dbo.Policy
WHERE LastName LIKE 'A%'

Table Variables

Existing in memory for the duration of a single T-SQL batch, table variables are declared using syntax similar to local variable declaration.
How to Create:

DECLARE @MyTableVariable TABLE 
(
 PolicyId INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
 LastName VARCHAR(50) NOT NULL 
)

How to Use:

INSERT INTO @MyTableVariable
SELECT PolicyId, LastName 
FROM dbo.Policy
WHERE LastName LIKE 'A%'

Once a GO command is issued from Query Analyzer, the table variable is no longer in scope for any SQL statement issued afterwards. A table variable can be created at the beginning of a stored procedure and referenced throughout since everything declared within the stored procedure remains in scope throughout the lifetime of the stored procedure execution