依據 SQL 腳本修改資料庫表結構：保留數據的升級方案實戰

專案開發中，常面臨舊版資料庫結構需升級至新版，且必須保留客戶現有數據的情境。本文分享三種演進方案，從手動操作到自動化工具，逐步優化升級流程。

情境說明

客戶使用舊版資料庫已有一段時間，累積一定業務數據。新版本需求導致資料庫結構大幅變動：包含預存程序調整、新增資料表、欄位型別變更、欄位新增等。挑戰在於：如何在不遺失舊數據的前提下，將客戶端資料庫結構平滑升級？

方案一：匯出腳本 + 重建資料庫 + 數據遷移（初期測試可行，正式環境不實用）

• 使用 SSMS 產生新版資料庫完整建構腳本。
• 將舊資料庫重新命名，執行新版腳本建立新資料庫。
• 透過匯入/匯出精靈或 INSERT INTO ... SELECT 將舊數據遷移至新表。

缺點： 數據量大時耗時極長，客戶端操作複雜，易出錯，需停機維護窗口過長。

方案二：表重命名 + 新建表 + 數據搬移（半自動化，核心 SQL 技巧）

核心邏輯：針對現有表，先改名備份，再依新腳本建表，最後將舊表數據寫入新表（僅對應同名欄位）。

關鍵 SQL 語法

-- 重命名表
EXEC sp_rename 'OldTableName', 'OldTableName_Bak';

-- 建立新表（依新版腳本）
CREATE TABLE NewTableName (...);

-- 搬移數據（指定欄位對應）
INSERT INTO NewTableName (Col1, Col2, ...)
SELECT Col1, Col2, ... FROM OldTableName_Bak;

輔助操作：物件重命名與約束管理

• 資料庫重命名： EXEC sp_renamedb 'OldDB', 'NewDB';
• 欄位重命名： EXEC sp_rename 'TableName.[OldCol]', 'NewCol', 'COLUMN';
• 查詢預存程序： SELECT name FROM sys.procedures;
• 查詢約束： SELECT constraint_name, table_name FROM information_schema.table_constraints;
• 刪除約束： ALTER TABLE TableName DROP CONSTRAINT [PK_|FK_|UK_|DF_|CK_]ConstraintName;

常見約束前綴命名規範

方案三：動態比對結構 + ALTER TABLE 就地修改（最終自動化工具雛形）

核心思路：讀取舊表結構與新版腳本結構，僅對差異執行 ALTER TABLE，完全避免數據搬移。

核心邏輯

• 查詢現有欄位：SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'TableName';
• 欄位存在但型別不同： ALTER TABLE [TableName] ALTER COLUMN [ColName] [NewType] NOT NULL;
• 新增欄位： ALTER TABLE [TableName] ADD [ColName] [Type] NOT NULL;
• 輔以正則表達式解析腳本中的 DDL 指令，配合 ADO.NET 批次執行。

現存限制與後續優化方向

• 未處理舊表多餘欄位的刪除（需謹慎評估影響）。
• 容錯機制不足：缺乏交易回滾、進度續傳、詳細日誌記錄。
• 通用性有限：針對特定專案結構，難以直接套用至異構系統。

此演進過程展示了從人工腳本到自動化工具的思維轉變。實務上，成熟的資料庫遷移工具（如 Flyway, Liquibase, DbUp）已提供版本化、冪等、可回滾的遷移框架，建議新專案優先採用成熟方案，避免重複造輪子。