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>