SQL SELECT INTO 語句詳解：表備份、跨庫複製與數據導出實戰

SELECT INTO 核心功能

SELECT INTO 從源表查詢數據並直接創建新表存儲結果，常用於：

• 表結構+數據完整備份
• 跨數據庫/跨服務器數據複製
• 子集數據提取構建報表/測試數據集
• 大表分區/歸檔歷史數據

語法差異：數據庫系統不兼容性極大

SQL Server 原生 SELECT INTO 語法

完整複製表結構與數據

-- 備份整表
SELECT * 
INTO persons_backup 
FROM persons;

-- 指定目標數據庫 (需權限)
SELECT * 
INTO backup_db.dbo.persons_backup 
FROM persons;

選擇性複製列與行

-- 僅複製指定列
SELECT last_name, first_name, city
INTO persons_simple
FROM persons;

-- 帶 WHERE 條件過濾
SELECT last_name, first_name, city
INTO persons_sandnes
FROM persons
WHERE city = 'Sandnes';

-- 多表聯結結果存入新表
SELECT p.last_name, o.order_no
INTO persons_orders_backup
FROM persons p
INNER JOIN orders o ON p.p_id = o.p_id;

MySQL/PostgreSQL/Oracle/SQLite 通用替代方案

方案 1：CREATE TABLE AS SELECT (CTAS) - 推薦

-- MySQL / PostgreSQL / Oracle / SQLite 通用
-- 完整複製
CREATE TABLE persons_backup AS 
SELECT * FROM persons;

-- 選擇列、過濾行、聯結查詢
CREATE TABLE persons_simple AS
SELECT last_name, first_name, city
FROM persons
WHERE city = 'Sandnes';

CREATE TABLE persons_orders_backup AS
SELECT p.last_name, o.order_no
FROM persons p
INNER JOIN orders o ON p.p_id = o.p_id;

方案 2：CREATE TABLE + INSERT INTO SELECT - 精確控制結構

-- 步驟 1：精確定義目標表結構 (含索引、約束、註釋)
CREATE TABLE persons_backup (
    p_id INT PRIMARY KEY,
    last_name VARCHAR(255) NOT NULL,
    first_name VARCHAR(255),
    address VARCHAR(255),
    city VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB COMMENT='人員表備份';

-- 步驟 2：插入數據
INSERT INTO persons_backup (p_id, last_name, first_name, address, city)
SELECT p_id, last_name, first_name, address, city
FROM persons
WHERE city = 'Sandnes';

方案 3：mysqldump / pg_dump / expdp - 生產級備份

# MySQL 邏輯備份 (結構+數據)
mysqldump -u root -p db_name persons > persons_backup.sql

# MySQL 只導出數據 (無建表語句)
mysqldump -u root -p --no-create-info db_name persons > persons_data.sql

# PostgreSQL
pg_dump -U postgres -t persons db_name > persons_backup.sql

# Oracle Data Pump
expdp system/password tables=persons dumpfile=persons.dmp

進階應用場景

1. 歷史數據歸檔 (分區表模擬)

-- 將 2023 年前訂單歸檔到獨立表
CREATE TABLE orders_archive_2023 AS
SELECT * FROM orders
WHERE order_date < '2024-01-01';

-- 歸檔後從主表刪除 (大表分批刪除避免長事務)
DELETE FROM orders 
WHERE order_date < '2024-01-01'
LIMIT 10000;  -- 循環執行直到受影響行數為 0

2. 測試數據集構建

-- 抽取 10% 樣本數據用於開發測試
CREATE TABLE persons_sample AS
SELECT * FROM persons
WHERE RAND() < 0.1;  -- MySQL
-- PostgreSQL: WHERE random() < 0.1
-- SQL Server: WHERE CAST(CHECKSUM(NEWID()) AS FLOAT) / 2147483647 < 0.1

3. 寬表構建 - 多表預聯結物化視圖

-- 客戶-訂單-產品寬表，供 BI 直接查詢
CREATE TABLE customer_order_wide AS
SELECT 
    c.customer_id, c.name AS customer_name, c.email, c.city,
    o.order_id, o.order_date, o.total_amount, o.status,
    p.product_id, p.product_name, p.category, p.price,
    oi.quantity, oi.unit_price AS item_price
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
LEFT JOIN order_items oi ON o.order_id = oi.order_id
LEFT JOIN products p ON oi.product_id = p.product_id;

關鍵限制與注意事項

• 不複製約束：SELECT INTO / CTAS 僅複製列定義和數據，不複製主鍵、外鍵、索引、觸發器、權限、註釋
• 不複製自增屬性：目標表自增列變為普通列
• 大表執行時間長：百萬行以上建議分批或使用導出導入工具
• 事務日誌膨脹：SQL Server 簡單恢復模式下最小日誌，完整模式下全量記錄
• 權限要求：目標數據庫需 CREATE TABLE 權限

最佳實踐對比總結

總結

SELECT INTO 僅在 SQL Server/Access 中原生支持，主流開源數據庫 (MySQL/PostgreSQL/Oracle/SQLite) 統一使用 CREATE TABLE AS SELECT (CTAS) 或 CREATE TABLE + INSERT SELECT 替代。生產環境備份首選專業導出工具，開發測試場景 CTAS 最便捷。核心認知：**CTAS 只複製「列定義+數據」，約束/索引/權限需手動補充或通過工具遷移。**