SQL Server/T-SQL/Table/Temporary Table
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
1> create table employee(
2> ID int,
3> name nvarchar (10),
4> salary int,
5> start_date datetime,
6> city nvarchar (10),
7> region char (1))
8> GO
1>
2> insert into employee (ID, name, salary, start_date, city, region)
3> values (1, "Jason", 40420, "02/01/94", "New York", "W")
4> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (2, "Robert",14420, "01/02/95", "Vancouver","N")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (3, "Celia", 24020, "12/03/96", "Toronto", "W")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (4, "Linda", 40620, "11/04/97", "New York", "N")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (5, "David", 80026, "10/05/98", "Vancouver","W")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (6, "James", 70060, "09/06/99", "Toronto", "N")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (7, "Alison",90620, "08/07/00", "New York", "W")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (8, "Chris", 26020, "07/08/01", "Vancouver","N")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (9, "Mary", 60020, "06/09/02", "Toronto", "W")
3> GO
(1 rows affected)
1>
2> select * from employee
3> GO
ID name salary start_date city region
----------- ---------- ----------- ----------------------- ---------- ------
1 Jason 40420 1994-02-01 00:00:00.000 New York W
2 Robert 14420 1995-01-02 00:00:00.000 Vancouver N
3 Celia 24020 1996-12-03 00:00:00.000 Toronto W
4 Linda 40620 1997-11-04 00:00:00.000 New York N
5 David 80026 1998-10-05 00:00:00.000 Vancouver W
6 James 70060 1999-09-06 00:00:00.000 Toronto N
7 Alison 90620 2000-08-07 00:00:00.000 New York W
8 Chris 26020 2001-07-08 00:00:00.000 Vancouver N
9 Mary 60020 2002-06-09 00:00:00.000 Toronto W
(9 rows affected)
1>
2> --Create a temporary table by starting the table name with a pound sign (#) or double pound sign (##). A single pound sign denotes a local temporary object that is only visible
from the current connection.
3> --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.
4>
5> SELECT * INTO ##MyContacts
6> FROM Employee
7> GO
1>
2> select * from ##MyContacts
3> GO
ID name salary start_date city region
----------- ---------- ----------- ----------------------- ---------- ------
1 Jason 40420 1994-02-01 00:00:00.000 New York W
2 Robert 14420 1995-01-02 00:00:00.000 Vancouver N
3 Celia 24020 1996-12-03 00:00:00.000 Toronto W
4 Linda 40620 1997-11-04 00:00:00.000 New York N
5 David 80026 1998-10-05 00:00:00.000 Vancouver W
6 James 70060 1999-09-06 00:00:00.000 Toronto N
7 Alison 90620 2000-08-07 00:00:00.000 New York W
8 Chris 26020 2001-07-08 00:00:00.000 Vancouver N
9 Mary 60020 2002-06-09 00:00:00.000 Toronto W
(9 rows affected)
1>
2> drop table employee
3> GO
1>
Create temporary table for numbers
-- Create temporary table for numbers:
Create Table #ASCIIVals (ASCIIValue SmallInt)
-- Insert numbers 0 - 127 into table:
Insert Into #ASCIIVals (ASCIIValue) Select 0
Insert Into #ASCIIVals (ASCIIValue) Select 1
Insert Into #ASCIIVals (ASCIIValue) Select 2
Insert Into #ASCIIVals (ASCIIValue) Select 3
Insert Into #ASCIIVals (ASCIIValue) Select 4
Insert Into #ASCIIVals (ASCIIValue) Select 123
Insert Into #ASCIIVals (ASCIIValue) Select 124
Insert Into #ASCIIVals (ASCIIValue) Select 125
Insert Into #ASCIIVals (ASCIIValue) Select 126
Insert Into #ASCIIVals (ASCIIValue) Select 127
-- Return all integer values and corresponding ASCII characters:
SELECT ASCIIValue, CHAR(ASCIIValue) As Character FROM #ASCIIVals
Local temporary tables are removed at the end of the current session
1>
2>
3> CREATE TABLE project (project_no CHAR(4) NOT NULL,
4> project_name CHAR(15) NOT NULL,
5> budget FLOAT NULL)
6> GO
1> insert into project values ("p1", "Search Engine", 120000.00)
2> insert into project values ("p2", "Programming", 95000.00)
3> insert into project values ("p3", "SQL", 186500.00)
4> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
1> select * from project
2> GO
project_no project_name budget
---------- --------------- ------------------------
p1 Search Engine 120000
p2 Programming 95000
p3 SQL 186500
(3 rows affected)
1>
2> -- Temporary Tables
3>
4> -- Local temporary tables are removed at the end of the current session.
5> -- They are specified with the prefix #
6>
7> -- Global temporary tables, which are specified with the prefix ##, are dropped at the end of the session that created this table.
8>
9>
10>
11> SELECT project_no, project_name
12> INTO #project_temp
13> FROM project
14> GO
(3 rows affected)
1>
2> select * from #project_temp
3> GO
project_no project_name
---------- ---------------
p1 Search Engine
p2 Programming
p3 SQL
(3 rows affected)
1>
2> drop table #project_temp
3> drop table project
4> GO
1>