實用 SQL 範例集：複製表、刪除重複、備份、主外鍵、日期函數

1. 複製表資料與結構

-- 複製指定欄位（結構需相同）
INSERT INTO TestT1 (a1, b1, c1) SELECT a, b, c FROM Test;

-- 全欄位複製（結構完全相同）
INSERT INTO TestT SELECT * FROM Test;

-- 複製單筆並指定固定值
INSERT INTO a SELECT '123' AS id, temp, temp2 FROM b WHERE id = 10;

-- Oracle: 序列產生主鍵
INSERT INTO notebook (id, title, content)
SELECT notebook_sequence.NEXTVAL, first_name, last_name FROM students;

2. 查找與刪除重複記錄

-- 查找重複 (Oracle ROWID)
SELECT DRAWING, DSNO FROM EM5_PIPE_PREFAB
WHERE ROWID != (SELECT MAX(ROWID) FROM EM5_PIPE_PREFAB D
                WHERE EM5_PIPE_PREFAB.DRAWING = D.DRAWING
                  AND EM5_PIPE_PREFAB.DSNO = D.DSNO);

-- 刪除重複，保留 ROWID 最大者
DELETE FROM EM5_PIPE_PREFAB
WHERE ROWID != (SELECT MAX(ROWID) FROM EM5_PIPE_PREFAB D
                WHERE EM5_PIPE_PREFAB.DRAWING = D.DRAWING
                  AND EM5_PIPE_PREFAB.DSNO = D.DSNO);

SQL Server 版本請用主鍵或 ROW_NUMBER()。

3. 僅複製表結構

-- Oracle
CREATE TABLE new_table AS SELECT * FROM old_table WHERE ROWNUM < 1;

-- SQL Server
SELECT * INTO new_table FROM old_table WHERE 1 <> 1;

4. 一次刪除多欄位

ALTER TABLE TEST_A DROP (TEST_num, test_name);  -- Oracle
ALTER TABLE TEST_A DROP COLUMN TEST_num, DROP COLUMN test_name;  -- SQL Server

5. 建立備份表

CREATE TABLE TableName_Bak AS SELECT * FROM TableName;  -- Oracle
SELECT * INTO TableName_Bak FROM TableName;  -- SQL Server

6. 主外鍵關聯建立範例

-- 主表
CREATE TABLE t1 (id INT, stu_name VARCHAR(50));
ALTER TABLE t1 ADD CONSTRAINT pk_t1 PRIMARY KEY (id);

-- 從表
CREATE TABLE t2 (id INT PRIMARY KEY, sex VARCHAR(10), address VARCHAR(50));
ALTER TABLE t2 ADD CONSTRAINT fk_t2_t1 FOREIGN KEY (id) REFERENCES t1(id) ON DELETE CASCADE;

-- 測試：刪除主表 → 從表級聯刪除
DELETE FROM t1;  -- t2 同步清空

7. 日期時間常用函數

8. 實用片段：動態建立資料表

DECLARE @sql NVARCHAR(MAX) = N'CREATE TABLE ' + QUOTENAME(@tbl) + N' (id INT PRIMARY KEY, name NVARCHAR(100))';
EXEC sp_executesql @sql;

結語

收集常用 SQL 片段並分類整理，能大幅提升開發與維運效率。建議團隊建立共用 Snippet 庫（如 GitHub Gist、Notion、VS Code Snippets），持續累積沉澱。