Programming in MS SQL Server 2012

Temporary Table

Temporary table is similar to table object in SQL Server. By default, table object is stores in user defined database, yet temporary table is stores in tempdb system database. Local temporary table must define with # sign like # TableName as well as Global temporary table must define with ## signs like ## TableName.
Local temporary table records automatically erase from the memory once complete the particular user defined function or stored procedure execution. Global temporary table records erase from the memory when restart SQL Server services.
If a temporary table is created with a named constraint and the temporary table is created within the scope of a user-defined transaction, only one user at a time can execute the statement that creates the temp table. For example, if a stored procedure creates a temporary table with a named primary key constraint, the stored procedure cannot be executed simultaneously by multiple users.
The following example describes a #Local Temporary Table.

-- Example 81 --

CREATE TABLE #TESTTABLE

(

EmpNumber INT,

EmpName VARCHAR(50)

)

SELECT * FROM #TESTTABLE

INSERT INTO #TESTTABLE VALUES(1234501,'EMP01');

INSERT INTO #TESTTABLE VALUES(1234502,'EMP02');

INSERT INTO #TESTTABLE VALUES(1234503,'EMP03');

INSERT INTO #TESTTABLE VALUES(1234504,'EMP04');

INSERT INTO #TESTTABLE VALUES(1234505,'EMP05');

SELECT * FROM #TESTTABLE

UPDATE #TESTTABLE SET EmpName = 'Employee 01' WHERE EmpNumber = 1234501

UPDATE #TESTTABLE SET EmpName = 'Employee 02' WHERE EmpNumber = 1234502

UPDATE #TESTTABLE SET EmpName = 'Employee 03' WHERE EmpNumber = 1234503

UPDATE #TESTTABLE SET EmpName = 'Employee 04' WHERE EmpNumber = 1234504

UPDATE #TESTTABLE SET EmpName = 'Employee 05' WHERE EmpNumber = 1234505

SELECT * FROM #TESTTABLE

DELETE FROM #TESTTABLE

SELECT * FROM #TESTTABLE

COMMIT TRANSACTION

DROP TABLE #TESTTABLE

Query Output Screen

* * * * *

Your Comment To AUTHOR