There are a few options when creating a table as part of a SQL operation.
Notes:
- The highlighted symbol is the indicator of the type of table.
- Temp table and table variable declaration/creation syntax is different
1.) Temp Table - This temp table is only active during the execution of the query and does not persist
DECLARE @tmp TABLE
(
Id
INT,
Description
VARCHAR(32)
)
INSERT INTO @tmp
(ID, Description)
VALUES (1, 'test
temp table')
2.) Table Variable - This table will need DROPped as it will persist
CREATE TABLE #tmp
(
Id
INT,
Description
VARCHAR(32)
)
INSERT INTO #tmp
(ID, Description)
VALUES (1, 'test table variable')
DROP TABLE #tmp
3.) Global Table Variable - This table will be accessible across query windows. This table will need DROPped as it will persist.
CREATE TABLE
##globalTmp
(
Id
INT,
Description
VARCHAR(32)
)
INSERT INTO
##globalTmp (ID, Description)
VALUES (1, 'test global table variable')
DROP TABLE
##globalTmp