SQL Server 資料表操作完整指南：INSERT、UPDATE、DELETE 深度解析

T-SQL 資料操作語言 (DML) 核心三大指令全解析

掌握 INSERT、UPDATE、DELETE 的完整語法、行為細節與最佳實務，是資料庫開發者的基本功。本文系統整理語法選項、隱性行為 (DEFAULT/IDENTITY/NULL)、錯誤處理與效能考量。

一、INSERT — 插入新資料列

完整語法結構

INSERT [INTO] {table_name | view_name} 
    [ ( column_list ) ]
    { 
        VALUES ( { DEFAULT | expression } [ ,...n ] )
      | DEFAULT VALUES
      | select_statement
      | execute_statement
    }

四種插入模式詳解

隱性欄位處理規則 (未在 column_list 中)

1. IDENTITY / timestamp：引擎自動產生
2. 有 DEFAULT 约束：使用預設值
3. 允許 NULL：寫入 NULL
4. 無預設且 NOT NULL：報錯，整批回滾

效能與最佳實務

• 大量匯入：BULK INSERT / bcp / SqlBulkCopy (最小記錄、鎖表、需權限)
• 最小記錄插入：INSERT INTO T WITH (TABLOCK) SELECT ... (簡單/大量記錄模式)
• 避免單筆迴圈：應用層打包批次 (如 1000 筆/批) 或使用表值參數 (TVP)

二、UPDATE — 修改現有資料

核心語法 (常用簡化版)

UPDATE table_name
SET col1 = expr1, col2 = expr2, ...
[FROM table_source]
[WHERE search_condition];

關鍵行為與陷阱

• 無 WHERE = 全表更新：極度危險，務必加 WHERE 1=0 測試
• IDENTITY 欄位 不可更新
• timestamp 自動更新，不可手動指定
• 大量更新分批：避免長鎖、記錄檔暴增、鎖升級
• FROM 子句支援多表關聯更新：

  UPDATE T
  SET T.Col = S.Col
  FROM Target T
  JOIN Source S ON T.ID = S.ID
  WHERE S.Flag = 1;

三、DELETE / TRUNCATE TABLE — 刪除資料

DELETE

DELETE [FROM] table_name [WHERE condition];

• 逐列記錄記錄檔，可 ROLLBACK，觸發 AFTER DELETE Trigger
• 支援 FROM 多表關聯刪除 (同 UPDATE)
• 不重置 IDENTITY 種子

TRUNCATE TABLE

TRUNCATE TABLE table_name;

• 釋放資料頁 (最小記錄)，不可回滾 (隱含交易)
• 重置 IDENTITY 至種子值
• 不觸發 Trigger
• 限制：被外鍵參考、參與索引檢視、異構複寫發佈時不可用

選擇決策表

實戰範例：UserTable 完整操作示範

-- 建表
CREATE TABLE UserTable (
    UserID INT IDENTITY(1,1) PRIMARY KEY,
    UserName NVARCHAR(20) NOT NULL,
    Gender NCHAR(2) DEFAULT N'未填',
    Age TINYINT NULL,
    RegDate DATETIME DEFAULT GETDATE()
);

-- 1. 插入 (多種方式)
INSERT UserTable (UserName, Age) VALUES (N'張三', 25);  -- 指定欄位
INSERT UserTable VALUES (N'李四', N'男', 30, DEFAULT);  -- 全欄位含 DEFAULT
DECLARE @Name NVARCHAR(20) = N'王五';
INSERT UserTable (UserName, Gender, Age) VALUES (@Name, N'女', 28);  -- 變數

-- 2. 修改
UPDATE UserTable SET Gender = N'男' WHERE Gender IS NULL;  -- 批次修正
UPDATE UserTable SET Age = Age + 1 WHERE UserID = 1;       -- 運算式更新

-- 3. 刪除
DELETE UserTable WHERE UserName = N'張三';
TRUNCATE TABLE UserTable;  -- 快速清空測試資料

總結：DML 鐵律

1. 永遠在 WHERE 先用 SELECT 驗證影響範圍
2. 大量操作分批、最小記錄、監控記錄檔
3. 善用 DEFAULT、IDENTITY、ROWVERSION 讓引擎分擔責任
4. 刪除前確認外鍵、Trigger、複寫相依性