資料表重新建立及資料遷移 SOP

💡 Purpose: 此 SOP 說明如何從原始資料表進行備份、建立新表、資料遷移以及重新命名，目的是因應原資料表主鍵未設定自動遞增，需先備份資料，再依新的 DDL 語法建立自動遞增主鍵的表結構，最後將資料還原。請務必在每個步驟執行前確認相關操作已備份完成，以避免資料遺失。

前置檢查

步驟 0：檢查表結構與資料筆數

1. 取得資料表結構，確認是否已有自動遞增主鍵。

   exec sp_columns WCSTXXX;

2. 檢查主鍵（例如：WCSID）是否有重複筆數：

   select WCSID, COUNT(*)
   from WCSTXXX
   group by WCSID
   having COUNT(*) > 1;

3. 檢查資料表的總筆數：

   select COUNT(*) from WCSTXXX;

注意： 請將檢查結果記錄下來，作為後續參考依據。

---

備份原始資料

步驟 1：備份現有資料表

將現有資料完整備份至另一張暫存表中，避免後續操作造成資料遺失：

select * into backup_WCSTXXX from WCSTXXX;

---

建立新資料表

步驟 2：產出並修改 DDL 語法

1. 從現有資料表產生 DDL 語法，並依下列需求修改：

   • 修改TABLE NAME（例如：由 WCSTXXX 變更為 WCSTXXX_New）
   • 修改主鍵敘述（PRIMARY KEY CLUSTERED），設定主鍵欄位 WCSID 為自動遞增（identity(1,1)）

2. 修改後的範例如下（僅示範部分語法，其他欄位請依實際情形補上）：

   USE [WCS];
   GO
   
   /****** Object:  Table [dbo].[WCSTXXX]    Script Date: 02/24/2025 15:17:24 ******/
   SET ANSI_NULLS ON;
   GO
   
   SET QUOTED_IDENTIFIER ON;
   GO
   
   CREATE TABLE [dbo].[WCSTXXX_New](
       [WCSID] [int] identity(1,1) NOT NULL,
       -- 其他欄位請依照原資料表定義補上，例如：
       [TITLE] NVARCHAR(255),
       [CONTENT] NVARCHAR(MAX),
       [PUBLISH_DATE] DATETIME,
       [EXTRACT_DATE] DATETIME,
       [WEBURL] NVARCHAR(255),
    CONSTRAINT [PK_WCSTXXX] PRIMARY KEY CLUSTERED
   (
       [WCSID] ASC
   ) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
   ) ON [PRIMARY];
   GO

提醒： 確認語法修改正確後再執行建立新表的指令。

---

資料遷移

步驟 3：將備份資料寫入新建立的表

將備份表中的資料寫入新表（注意：此處只針對非自動遞增的欄位進行遷移）：

INSERT INTO WCSTXXX_New (TITLE, CONTENT, PUBLISH_DATE, EXTRACT_DATE, WEBURL)
select TITLE, CONTENT, PUBLISH_DATE, EXTRACT_DATE, WEBURL
from backup_WCSTXXX;

步驟 4：清空原始資料表

請再次確認備份完成後，使用 TRUNCATE 清空原始資料表，確保無遺漏資料：

truncate table WCSTXXX;

步驟 5：將新資料寫回原始表

由於原資料表原先沒有自動遞增主鍵，因此在備份時主鍵欄位已有值，需用事前準備好的 DML 語法將資料（包含主鍵）寫回原表。

範例如下（請依實際欄位補全 DML 語法）：

INSERT [dbo].[WCSTXXX] ([WCSID], TITLE, CONTENT, PUBLISH_DATE, EXTRACT_DATE, WEBURL)
-- 此處資料來源依照需求調整
VALUES
( ... ),
( ... );

步驟 6：將原始表最新資料再寫入新表

完成原始表資料寫入後，再將最新資料遷移到新建立的表，確保兩表資料同步：

INSERT INTO WCSTXXX_New (TITLE, CONTENT, PUBLISH_DATE, EXTRACT_DATE, WEBURL)
select TITLE, CONTENT, PUBLISH_DATE, EXTRACT_DATE, WEBURL
from WCSTXXX;

---

最終確認與表結構變更

步驟 7：檢查筆數

確認新表、備份表及原始表的筆數，確保資料一致：

select count(*) from WCSTXXX_New;
select count(*) from backup_WCSTXXX;
select count(*) from WCSTXXX;

步驟 8：刪除原始表

在確認資料完全遷移並備份無誤後，可刪除原始資料表：

drop table WCSTXXX;

步驟 9：重新命名表格

將備份表與新建立的表重新命名，以達到以下目的：

• 將原始表備份命名為 WCSTXXX_Old
• 將新建立的表命名為原始表名稱 WCSTXXX執行以下指令：

EXEC sp_rename 'backup_WCSTXXX', 'WCSTXXX_Old';
EXEC sp_rename 'WCSTXXX_New', 'WCSTXXX';

---

備註

• 執行前確認： 每個步驟執行前請務必確認所有資料均已備份，避免操作失誤造成資料遺失。
• 程式碼調整： 此文件中的 SQL 範例僅供參考，請依據實際資料表結構與業務需求進行調整。
• 版本控管： 建議在操作前後記錄相關版本及執行日期，方便後續追蹤與還原。

資料正確性驗證

步驟 10：快速驗證資料正確性

1. 使用 TOP 指令抽樣檢查資料：

   -- 檢查新舊表格的前N筆資料是否一致
   SELECT TOP 10 *
   FROM WCSTXXX
   ORDER BY WCSID;
   
   SELECT TOP 10 *
   FROM WCSTXXX_Old
   ORDER BY WCSID;

2. 使用 CHECKSUM 比對資料：

   -- 使用CHECKSUM比對重要欄位
   SELECT CHECKSUM_AGG(CHECKSUM(*))
   FROM (SELECT TITLE, CONTENT, PUBLISH_DATE, EXTRACT_DATE, WEBURL
         FROM WCSTXXX) AS T1;
   
   SELECT CHECKSUM_AGG(CHECKSUM(*))
   FROM (SELECT TITLE, CONTENT, PUBLISH_DATE, EXTRACT_DATE, WEBURL
         FROM WCSTXXX_Old) AS T2;

3. 檢查關鍵欄位的統計資訊：

   -- 檢查日期範圍
   SELECT MIN(PUBLISH_DATE), MAX(PUBLISH_DATE), COUNT(*)
   FROM WCSTXXX;
   
   -- 檢查NULL值數量
   SELECT 
       COUNT(CASE WHEN TITLE IS NULL THEN 1 END) as NULL_TITLE_COUNT,
       COUNT(CASE WHEN CONTENT IS NULL THEN 1 END) as NULL_CONTENT_COUNT
   FROM WCSTXXX;