MSSQL 資料導入最佳實務：從暫存表到正式表的規範流程

核心原則

資料導入不是「一次到位」，而是分層驗證、逐步修正的管線作業。任何跳過暫存階段、直接寫入正式表的操作，都是技術債的來源。

第一層：原始資料落地暫存表

• 所有來源欄位統一定義為 VARCHAR(MAX) / NVARCHAR(MAX)，接受任何髒資料（非數字塞進數值欄、超長字串、編碼亂碼）。
• 使用 BULK INSERT、bcp、SSIS 平面檔案來源、或 OPENROWSET 直接導入，不做任何轉換。
• 暫存表命名慣例：stg_<來源系統>_<實體>_<YYYYMMDD>，欄位名保留原始名稱（含空格/特殊字元），必要時用 [] 括住。

第二層：清洗轉換至標準化暫存表

• 建立結構與正式表完全一致的暫存表（tmp_<實體>），欄位型別、長度、NULL/NOT NULL、DEFAULT、CHECK、PK/FK 全部對齊。
• 撰寫 INSERT INTO tmp_... SELECT ... FROM stg_...，在 SELECT 列清單中進行：

-- 範例：地址拆分、型別轉換、預設值填補
SELECT
    TRY_CAST(REPLACE(raw_id, 'ID-', '') AS INT)          AS Id,
    LTRIM(RTRIM(raw_name))                                AS Name,
    CASE WHEN TRY_CAST(raw_amt AS DECIMAL(18,2)) IS NULL THEN 0 ELSE TRY_CAST(raw_amt AS DECIMAL(18,2)) END AS Amount,
    COALESCE(NULLIF(raw_date, ''), '1900-01-01')         AS CreatedDate,
    UPPER(LEFT(raw_status, 1))                            AS StatusCode
FROM stg_source;

• 優勢：原始髒資料與清洗後資料並存，可隨時比對、稽核、回滾；轉換失敗以 TRY_CAST/NULLIF 捕獲而非中斷批次。

第三層：約束驗證（在正式表副本上）

1. 在同一資料庫建立正式表結構完全相同的副本 dbo_<實體>_shadow，保留所有約束（PK、FK、CK、DEFAULT、NOT NULL）。
2. INSERT INTO dbo_... SELECT * FROM tmp_...，觀察錯誤訊息：

• PK 衝突 → 來源有重複鍵，需去重或業務確認。
• FK 違反 → 參照資料缺失，需先導入主檔或設定預設值。
• CK 失敗 → 業務規則不符，需回溯來源系統修正。

第四層：實務資料驗證（含應用層）

• 將副本資料導入測試/預發環境，導向應用程式執行端到端測試（UI 操作、報表跑批、API 回歸）。
• 若原系統約束鬆散、新系統嚴格，「壞資料會進入原系統並破壞遷移」（Tom Moreau 語），此階段必須攔截。

第五層：正式導入

• 所有測試通過後，在維護窗口執行：

BEGIN TRAN;
    -- 可選：切換分區/切換表、或 TRUNCATE 正式表
    INSERT INTO dbo.<實體> SELECT * FROM tmp_<實體>;
    -- 或 MERGE 進行 UPSERT
COMMIT TRAN;

• 導入後立即執行 sp_updatestats、DBCC UPDATEUSAGE、重建碎片索引。

命名與編碼規範速查表

總結

「慢工出細活」：多花 30% 時間建立分層暫存與驗證管線，能省下 90% 的上線後火場修復成本。這份行為規範適用於 SQL Server 7.0 至最新版，亦可推廣至異構資料庫遷移專案。