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 / PostgreSQL

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

Oracle

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

• ← Back: DROP vs TRUNCATE
• Next: SELECT Statement →