Creating Temporary Tables

Exploratory Data Analysis in SQL

Christina Maimone

Data Scientist

Syntax

Create Temp Table Syntax

-- Create table as
CREATE TEMP TABLE new_tablename AS 
-- Query results to store in the table
SELECT column1, column2 
  FROM table;

Select Into Syntax

-- Select existing columns
SELECT column1, column2 
  -- Clause to direct results to a new temp table
  INTO TEMP TABLE new_tablename 
  -- Existing table with exisitng columns
  FROM table;

Create a table

CREATE TEMP TABLE top_companies AS
SELECT rank, 
       title 
  FROM fortune500
 WHERE rank <= 10;

SELECT * 
  FROM top_companies;

 rank |       title        
------+--------------------
    1 | Walmart
    2 | Berkshire Hathaway
    3 | Apple
    4 | Exxon Mobil
    5 | McKesson
    6 | UnitedHealth Group
    7 | CVS Health
    8 | General Motors
    9 | AT&T
   10 | Ford Motor
(10 rows)

Insert into table

INSERT INTO top_companies
SELECT rank, title 
  FROM fortune500 
 WHERE rank BETWEEN 11 AND 20;

SELECT * FROM top_companies;

 rank |          title           
------+--------------------------
    1 | Walmart
    2 | Berkshire Hathaway
    3 | Apple
...
    9 | AT&T
   10 | Ford Motor
   11 | AmerisourceBergen
   12 | Amazon.com
   13 | General Electric
   14 | Verizon
   15 | Cardinal Health
   16 | Costco
   17 | Walgreens Boots Alliance
   18 | Kroger
   19 | Chevron
   20 | Fannie Mae
(20 rows)

Delete (drop) table

DROP TABLE top_companies;

DROP TABLE IF EXISTS top_companies;

Time to create some tables!

Exploratory Data Analysis in SQL