SQL Server/T-SQL Tutorial/Table/Temporary Table
Содержание
- 1 A global temporary table"s name begins with ##.
- 2 A script that creates a global temporary table of random numbers
- 3 A script that uses a local temporary table instead of a derived table
- 4 Creating a Local Temporary Table with a SELECT INTO
- 5 Creating a Table with Duplicate Rows
- 6 Local temporary Table
- 7 SELECT INTO a temporary table
- 8 Temporary Tables
- 9 Using a Temporary Table to Communicate with an EXEC()
A global temporary table"s name begins with ##.
Creating a Global Temporary Table
6>
7> CREATE TABLE ##CustomerSales
8> (
9> ID char (6) NOT NULL,
10> Year smallint NOT NULL,
11> Sales money NOT NULL
12> )
13> GO
1>
The global temporary table will be dropped when the session that created it ends and
when all other processes that reference it have stopped referencing it.
3>
4> drop table ##CustomerSales
5> GO
A script that creates a global temporary table of random numbers
6>
7> CREATE TABLE ##RandomSSNs
8> (SSN_ID int IDENTITY,
9> SSN char(9) DEFAULT LEFT(CAST(CAST(CEILING(RAND()*10000000000)AS bigint)AS varchar),9))
10> GO
1>
2> INSERT ##RandomSSNs VALUES (DEFAULT)
3> INSERT ##RandomSSNs VALUES (DEFAULT)
4> GO
(1 rows affected)
(1 rows affected)
1>
2> SELECT * FROM ##RandomSSNs
3> GO
SSN_ID SSN
----------- ---------
1 515035498
2 432526378
(2 rows affected)
1>
2> drop table ##RandomSSNs;
3> GO
A script that uses a local temporary table instead of a derived table
7>
8> create table Billings (
9> BankerID INTEGER,
10> BillingNumber INTEGER,
11> BillingDate datetime,
12> BillingTotal INTEGER,
13> TermsID INTEGER,
14> BillingDueDate datetime ,
15> PaymentTotal INTEGER,
16> CreditTotal INTEGER
17>
18> );
19> GO
1>
2> INSERT INTO Billings VALUES (1, 1, "2005-01-22", 165, 1,"2005-04-22",123,321);
3> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (2, 2, "2001-02-21", 165, 1,"2002-02-22",123,321.);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (3, 3, "2003-05-02", 165, 1,"2005-04-12",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (4, 4, "1999-03-12", 165, 1,"2005-04-18",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (5, 5, "2000-04-23", 165, 1,"2005-04-17",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (6, 6, "2001-06-14", 165, 1,"2005-04-18",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (7, 7, "2002-07-15", 165, 1,"2005-04-19",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (8, 8, "2003-08-16", 165, 1,"2005-04-20",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (9, 9, "2004-09-17", 165, 1,"2005-04-21",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (0, 0, "2005-10-18", 165, 1,"2005-04-22",123,321);
2> GO
(1 rows affected)
1>
2> SELECT TOP 1 BankerID, AVG(BillingTotal) AS AvgBilling
3> INTO #TopBankers
4> FROM Billings
5> GROUP BY BankerID
6> ORDER BY AvgBilling DESC
7>
8>
9> SELECT Billings.BankerID, MAX(BillingDate) AS LatestInv
10> FROM Billings JOIN #TopBankers
11> ON Billings.BankerID = #TopBankers.BankerID
12> GROUP BY Billings.BankerID
13> GO
(1 rows affected)
BankerID LatestInv
----------- -----------------------
1 2005-01-22 00:00:00.000
(1 rows affected)
1>
2>
3> drop table Billings;
4> GO
1>
2>
Creating a Local Temporary Table with a SELECT INTO
15>
16> CREATE TABLE employee(
17> id INTEGER NOT NULL PRIMARY KEY,
18> first_name VARCHAR(10),
19> last_name VARCHAR(10),
20> salary DECIMAL(10,2),
21> start_Date DATETIME,
22> region VARCHAR(10),
23> city VARCHAR(20),
24> managerid INTEGER
25> );
26> GO
1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver",3);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown",4);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris",5);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London",6);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton",7);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary",8);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto",10);
2> GO
(1 rows affected)
1>
2> select * from employee;
3> GO
id first_name last_name salary start_Date region city managerid
----------- ---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver 3
2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown 4
3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris 5
4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London 6
5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton 7
6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary 8
7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York 9
8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina 9
9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto 10
(9 rows affected)
1>
2>
3>
4> SELECT
5> ID,
6> Start_Date
7> INTO
8> #OrderInfo
9> FROM
10> Employee
11>
12> select * from #OrderInfo
13> GO
(9 rows affected)
ID Start_Date
----------- -----------------------
1 2005-03-22 00:00:00.000
2 2003-07-21 00:00:00.000
3 2001-12-01 00:00:00.000
4 2006-03-03 00:00:00.000
5 2004-07-02 00:00:00.000
6 2002-05-19 00:00:00.000
7 2008-03-18 00:00:00.000
8 2007-07-17 00:00:00.000
9 2001-04-16 00:00:00.000
(9 rows affected)
1>
2>
3> drop table employee;
4> GO
1>
2>
Creating a Table with Duplicate Rows
4> CREATE TABLE Dupes(
5> ID int NOT NULL,
6> Txt char (10) NOT NULL
7> )
8> GO
1> INSERT Dupes (ID, Txt) VALUES (1, "x")
2> INSERT Dupes (ID, Txt) VALUES (1, "a")
3> INSERT Dupes (ID, Txt) VALUES (1, "x")
4> INSERT Dupes (ID, Txt) VALUES (1, "x")
5> INSERT Dupes (ID, Txt) VALUES (2, "b")
6> INSERT Dupes (ID, Txt) VALUES (2, "x")
7> INSERT Dupes (ID, Txt) VALUES (2, "b")
8> INSERT Dupes (ID, Txt) VALUES (3, "c")
9> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2>
3> --Creating a Temporary Table of Distinct Rows
4>
5> SELECT ID, Txt INTO #Singles
6> FROM
7> Dupes
8> GROUP BY
9> ID,
10> Txt
11> HAVING
12> COUNT (*) > 1;
13> GO
(2 rows affected)
1>
2> --Removing the Duplicates
3> DELETE d FROM Dupes AS D JOIN
4> #Singles AS S ON S.ID = D.ID
5> AND S.Txt = D.Txt;
6> GO
(5 rows affected)
1>
2> --Inserting the Former Duplicates
3>
4> INSERT Dupes
5> SELECT
6> *
7> FROM
8> #Singles
9>
10> drop table dupes;
11> GO
(2 rows affected)
1>
Local temporary Table
2> CREATE TABLE #config_out
3> (
4> name_col varchar(50),
5> minval int,
6> maxval int,
7> configval int,
8> runval int
9> )
10>
11> INSERT #config_out
12> EXEC sp_configure
13>
14> SELECT * FROM #config_out
15> GO
(15 rows affected)
name_col minval maxval configval runval
-------------------------------------------------- ----------- ----------- ----------- -----------
allow updates 0 1 1 0
clr enabled 0 1 0 0
cross db ownership chaining 0 1 0 0
default language 0 9999 0 0
max text repl size (B) 0 2147483647 65536 65536
nested triggers 0 1 1 1
remote access 0 1 1 1
remote admin connections 0 1 0 0
remote login timeout (s) 0 2147483647 20 20
remote proc trans 0 1 0 0
remote query timeout (s) 0 2147483647 600 600
server trigger recursion 0 1 1 1
show advanced options 0 1 0 0
user instances enabled 0 1 1 1
user options 0 32767 0 0
(15 rows affected)
SELECT INTO a temporary table
A single pound sign denotes a local temporary object that is only visible from the current connection.
A double pound sign denotes a global temporary object that is visible to all connections as long as the connection that created it is still active.
8>
9> CREATE TABLE employee(
10> id INTEGER NOT NULL PRIMARY KEY,
11> first_name VARCHAR(10),
12> last_name VARCHAR(10),
13> salary DECIMAL(10,2),
14> start_Date DATETIME,
15> region VARCHAR(10),
16> city VARCHAR(20),
17> managerid INTEGER
18> );
19> GO
1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver",3);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown",4);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris",5);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London",6);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton",7);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary",8);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto",10);
2> GO
(1 rows affected)
1>
2>
3> SELECT * INTO #MyContacts
4> FROM employee WHERE id = 1
5> GO
(1 rows affected)
1>
2>
3> drop table employee;
4> GO
1>
Temporary Tables
Each temporary table is implicitly dropped by the system.
Each temporary table is stored in the tempdb system database.
Temporary tables can be local or global.
Local temporary tables are removed at the end of the current session.
They are specified with the prefix #table_name.
Global temporary tables, which are specified with the prefix ##, are dropped at the end of the session that created this table.
CREATE TABLE #project_temp
(project_no CHAR(4) NOT NULL,
project_name CHAR(25) NOT NULL)
SELECT project_no, project_name
INTO #project_temp1
FROM project
Using a Temporary Table to Communicate with an EXEC()
7> CREATE TABLE #tmpvar
8> (
9> Variable varchar (25) NOT NULL
10> )
11>
12> DECLARE
13> @Variable varchar (25)
14>
15> EXEC ("INSERT INTO #tmpvar VALUES (""my value"")")
16>
17> SELECT
18> @Variable = Variable
19> FROM
20> #tmpvar
21>
22>
23> PRINT @Variable
24> GO
(1 rows affected)
(1 rows affected)
my value
1>
2> DROP TABLE #tmpvar
3> GO