Skip to content

COPY & TEMP Tables 📑

Prerequisites: CREATE TABLE, SELECT Statement

Mentor's Note: Sometimes you need a scratchpad. You don't want to mess up the main database, or you need to do some heavy calculations that require intermediate steps. That's where Temporary Tables shine. 💡

🌟 The Scenario: The Worksheet 📝

  • Main Table: The Final Exam Paper (Permanent).
  • Temp Table: The Rough Sheet (Exists only while you are working).
  • Copy Table: The Photocopy (A backup before you start grading).

💻 1. Creating a Temporary Table ⏳

Temp tables are visible only to the current session and are deleted automatically when you disconnect.

-- Standard # prefix or TEMP keyword
CREATE TEMP TABLE #Scratchpad (
    id INT,
    note VARCHAR(100)
);

-- Global Temp Table
CREATE GLOBAL TEMPORARY TABLE temp_scratchpad (
    id NUMBER,
    note VARCHAR2(100)
) ON COMMIT PRESERVE ROWS;

💻 2. Copying a Table (Cloning) 📸

There isn't a standard COPY command, but we use CREATE TABLE AS SELECT.

A. Copy Structure AND Data

CREATE TABLE customers_backup AS
SELECT * FROM customers;

B. Copy Structure ONLY (No Data)

We use a condition that is always false! 

CREATE TABLE customers_empty AS
SELECT * FROM customers
WHERE 1 = 0;

💡 Use Cases

  1. Complex Reporting: Load raw data into a Temp Table -> Clean it -> Join it -> Export result.
  2. Testing: Copy the Production table to a Test table -> Run your risky UPDATE query -> Verify -> Apply to Production.

📈 Learning Path