MSSQL — Local | Global Temp Table

Photo by Rubaitul Azad on Unsplash

🔖 文章索引

1. Local Temp Table
2. Global Temp Table
3. 一般 Table
4. CREATE Local Temp Table
5. CREATE Global Temp Table
6. Local Temp Table in Stored Procedure
7. Global Temp Table in Stored Procedure

Local Temp Table

只存在當前 session

Prefix with one (#) symbol.

e.g. #localTempTable

Global Temp Table

Prefix with (##) symbol.

e.g. ##globalTempTable

新增後存放的位置於

Database\System Databases\tempdb\Temporary Tables

一般 Table

IF (EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'Person1'
))
BEGIN
TRUNCATE TABLE Person1;
DROP TABLE Person1;
END;
GO
CREATE TABLE Person1
(
Id INT IDENTITY(1, 1) PRIMARY KEY NOT NULL,
[Name] NVARCHAR(20) NULL,
);

CREATE Local Temp Table

-- From SQL Server 2016, you can just use
DROP TABLE IF EXISTS #Person2;
-- From Previous version of SQL Server
IF OBJECT_ID('tempdb..#Person2') IS NOT NULL
BEGIN
TRUNCATE TABLE #Person2;
DROP TABLE #Person2;
END;
CREATE TABLE #Person2
(
Id INT IDENTITY PRIMARY KEY NOT NULL,
[Name] NVARCHAR(20) NULL
);

Check Temp Table information

SELECT *
FROM tempdb..sysobjects
WHERE name LIKE '#Person2%';

INSERT Local Temp Table

INSERT INTO #Person2
VALUES ('P2Name1'), ('P2Name2'), ('P2Name3');
SELECT *
FROM #Person2;

CREATE Global Temp Table

-- From SQL Server 2016, you can just use
DROP TABLE IF EXISTS ##Person3;
-- From Previous version of SQL Server
IF OBJECT_ID('tempdb..##Person3') IS NOT NULL
BEGIN
TRUNCATE TABLE ##Person3;
DROP TABLE ##Person3;
END;
CREATE TABLE ##Person3
(
Id INT IDENTITY PRIMARY KEY NOT NULL,
[Name] NVARCHAR(20) NULL
);

Check Temp Table information

SELECT *
FROM tempdb..sysobjects
WHERE name LIKE '##Person3%';

INSERT Global Temp Table

INSERT INTO ##Person3
VALUES ('P3Name1'), ('P3Name2'), ('P3Name3');
SELECT *
FROM ##Person3;

Local Temp Table in Stored Procedure

sp_ 開頭的都是系統預設

自己建立的 Stored Procedure 前面加 sp

IF (EXISTS(
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE'
AND LEFT(ROUTINE_NAME, 3) NOT IN ('sp_', 'xp_', 'ms_')
AND SPECIFIC_NAME = 'spLocalTempTable'
))
BEGIN
DROP PROCEDURE spLocalTempTable;
END;
GO
CREATE PROCEDURE spLocalTempTable
AS
BEGIN
IF OBJECT_ID('tempdb..#Person4') IS NOT NULL
BEGIN
TRUNCATE TABLE #Person4;
DROP TABLE #Person4;
END;
CREATE TABLE #Person4
(
Id INT IDENTITY(1, 1) PRIMARY KEY NOT NULL,
[Name] NVARCHAR(20) NULL
);
INSERT INTO #Person4
VALUES ('spP4Name1'), ('spP4Name2'), ('spP4Name3');
SELECT *
FROM #Person4;
END
GO
EXECUTE spLocalTempTable;
GO

Global Temp Table in Stored Procedure

##Global Temp Table will not be automatically dropped

on the completion of stored procedure execution.

sp_ 開頭的都是系統預設

自己建立的 Stored Procedure 前面加 sp

IF (EXISTS(
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE'
AND LEFT(ROUTINE_NAME, 3) NOT IN ('sp_', 'xp_', 'ms_')
AND SPECIFIC_NAME = 'spGlobalTempTable'
))
BEGIN
DROP PROCEDURE spGlobalTempTable;
END;
GO
CREATE PROCEDURE spGlobalTempTable
AS
BEGIN
IF OBJECT_ID('tempdb..##Person5') IS NOT NULL
BEGIN
TRUNCATE TABLE ##Person5;
DROP TABLE ##Person5;
END;
CREATE TABLE ##Person5
(
Id INT IDENTITY(1, 1) PRIMARY KEY NOT NULL,
[Name] NVARCHAR(20) NULL
);
INSERT INTO ##Person5
VALUES ('spP5Name1'), ('spP5Name2'), ('spP5Name3');
SELECT *
FROM ##Person5;
END
GO
EXECUTE spGlobalTempTable;
GO