2012年7月24日 星期二

複製資料到有自動增值欄位的資料表

我一直以為,在 SQL Server 複製資料表A 到資料表 B 不是件難事,直到遇到「有自動增值欄位的資料表」。

舉例來說,以下是兩個一模一樣的資料結構 TB1 與 TB2 :
CREATE TABLE [dbo].[TB1](
 [SN] [int] IDENTITY(1,1) NOT NULL,
 [SName] [nvarchar](50) NULL
) ON [PRIMARY]


CREATE TABLE [dbo].[TB2](
 [SN] [int] IDENTITY(1,1) NOT NULL,
 [SName] [nvarchar](50) NULL
) ON [PRIMARY]


當我執行
insert into TB2 select * from TB1
會遇到下面錯誤訊息:
位於資料表 'TB2' 的識別欄位其外顯值只有當使用了資料行清單且 IDENTITY_INSERT 為 ON 時才能指定。

為了能夠解決上述問題,必須暫時讓「有自動增值的欄位」變成沒有自動增值。可以到管理介面去變更資料表的定義,如下:(但記得匯完資料之後要記得改回去)

另外也可以透過程式來完成:
--表示在有自動增長欄位的情況下還允許新增
SET IDENTITY_INSERT  TB2 ON 

--要注意,不可以用 select * from TB1,
--必需詳細的指出欄位
insert into TB2 (SN,SName)
select SN,SName from TB1

--關閉在有自動增長欄位的情況下還允許新增
SET IDENTITY_INSERT  TB2 OFF 


需特別注意的地方有二:
一、透過 IDENTITY_INSERT ON/OFF 來完成
二、不可以用 select * from ,需明確指出各個欄位名稱,如 select SN, SName from

自己實際遇到的資料表內容,其實非常巨大。當我使用第一種方式去設定時,匯出資料沒問題,但當我要將「自動增長欄位」的欄位改回「是」的時候,SQL Server 就會跳出訊息,告訴我偵測到大量的資料,警告我會花很多時間,問我要不要等待,當然選要,結過,他跑一陣子之後就發出逾時的錯誤訊息而中斷工作,而自動增長欄位的設定也沒有改回去。遇到這情況,就一定得改用剛剛介紹的第二種方式來處理。


參考:
01:要將資料新增至有自動增值欄位的資料表中

沒有留言:

張貼留言