COPY & TEMP Tables 📑¶

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.

SQL Server / PostgreSQLOracle

-- 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¶

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