SQL Server 2005 - 關於 IDENTITY 的小技巧
最近，備份資料庫遇到一個問題，就是將舊資料搬移到新主機資料表的時候，如果主鍵是的 IDENTITY 是設定自動增加的話，那麼舊有的主鍵資料，在新資料表上面就好像是重新編號一樣又從第 1 筆開始塞到第 N 筆。
( PS. 我不是專業的 DBA 或是 DBD，所以我只會很笨的方法。)
例如 :
某台主機上舊有資料如下，C1 欄位是自動編號，但是可能經過使用者動作，可能只剩下 1、3、4、7、11 這幾筆。
塞到新資料表，C1 欄位就變成 1、2、3、4、5 完全走樣了。
後來發現原來要設定 IDENTITY_INSERT 的選項，才有辦法自己設定自動編號的欄位值。
語法如下：
-- 開啟 TestTable 的 IDENTITY_INSERT 屬性 SET IDENTITY_INSERT TestTable ON
-- 關閉 TestTable 的 IDENTITY_INSERT 屬性 SET IDENTITY_INSERT [TestTable] OFF
如果要重設資料表的 IDENTITY 的編號的話，則要使用 DBCC CHECKIDENT 指令來設定，以下為操作範例：
1: -- 將資料表資料刪除, 並重設 IDENTITY
2: TRUNCATE TABLE [TestTable]
3: 4: -- 開啟資料表可自行輸入 IDENTITY 的功能
5: SET IDENTITY_INSERT [TestTable] ON
6: 7: -- 準備資料來源
8: CREATE TABLE #tt (C1 int, C2 nvarchar(50) ,C3 nvarchar(50))
9: INSERT INTO #tt (C1, C2, C3) VALUES (1, '1', '2' )
10: INSERT INTO #tt (C1, C2, C3) VALUES (3, '2', '4' )
11: INSERT INTO #tt (C1, C2, C3) VALUES (4, '3', '6' )
12: INSERT INTO #tt (C1, C2, C3) VALUES (7, '4', '8' )
13: INSERT INTO #tt (C1, C2, C3) VALUES (11, '5', '10' )
14: 15: -- 將資料來源塞入目的資料表
16: INSERT INTO [TestTable] (C1, C2, C3)
17: SELECT * FROM #tt
18: 19: Drop table #tt
20: 21: -- 關閉資料表可自行輸入 IDENTITY 的功能, 由系統自行指定
22: SET IDENTITY_INSERT [TestTable] ON
23: 24: -- 將資料表的 IDENTITY 設為 11 (下一筆資料為 12)
25: DBCC CHECKIDENT (TestTable, reseed, 11)
26: DBCC CHECKIDENT (TestTable, reseed)
用以上語法產生新的 TestTable 就跟舊有的資料表一模一樣囉。