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 ##.
<source lang="sql">
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</source>
A script that creates a global temporary table of random numbers
<source lang="sql">
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</source>
A script that uses a local temporary table instead of a derived table
<source lang="sql">
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></source>
Creating a Local Temporary Table with a SELECT INTO
<source lang="sql">
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></source>
Creating a Table with Duplicate Rows
<source lang="sql">
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></source>
Local temporary Table
<source lang="sql">
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)</source>
SELECT INTO a temporary table
<source lang="sql">
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></source>
Temporary Tables
<source lang="sql">
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</source>
Using a Temporary Table to Communicate with an EXEC()
<source lang="sql">
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</source>
