none
新增資料列出現插入重複索引鍵 RRS feed

  • 問題

  • 請教各位前輩,目前有A, B兩個資料庫, A資料庫有一張表為T1(資料行id有設定PK); B資料庫有一張表為T2

    A資料庫的t1如下:

    id     name
    --------------
    1        abc
    2        org


    B資料庫的t2如下
    id     name
    --------------
    1        abc
    2        org
    3        omg
    4        osk

    要將B資料庫的T2裡面資料寫到A資料庫,使用的語法如下:

    set identity_insert A..t1 on   --已經將識別碼啟用,為何id的數值不會自動成長呢? 仍舊會出現重插入複索引鍵

    insert into a..t1 (id, name)

    select id, name from b..t2

    set identity_insert A..t1 off


    如上,已經將識別碼啟用,為何id的數值不會自動成長呢? 為何仍舊會出現重插入複索引鍵呢??

    請各位前輩幫小弟解惑,非常感謝!!!

    2009年11月29日 下午 02:30

解答

  • 另您所提供修正的:

    insert into a..t1 ([name])
    select [name] from b..t2

    如果只有這樣輸入,因為資料表id已經設定了PK,因此一定要開啟識別碼(set identity_insert on),沒這樣做的話錯誤訊息會顯示要開啟。

    不知道各位前輩是否有好的方式呢?

    似乎不太對...我反覆試了幾種情況,沒出現你說的「因為資料表 id 已經設定了 PK,因此一定要 set identity_insert on」,應該這麼說,
    SET IDENTITY_INSERT ON 只有在 id 設為識別值,並且你要自行指定一個值給它,跟是否為 PK 無關,例如:

    USE tempdb;
    GO
    
    CREATE TABLE t1
    (
    	id INT IDENTITY NOT NULL,
    	name NVarChar(20)
    )
    GO
    
    SET IDENTITY_INSERT dbo.t1 ON;
    INSERT INTO t1 (id, name) VALUES (1, 'abc');
    INSERT INTO t1 (id, name) VALUES (2, 'org');
    SET IDENTITY_INSERT dbo.t1 OFF;
    GO

    所以我判斷你在 t1 的 id 欄位有設定 IDENTITY 屬性。接下來你可以在執行以下語法:

    USE tempdb;
    GO
    
    CREATE TABLE t2
    (
    	id INT NOT NULL,
    	name NVarChar(20)
    )
    GO
    
    INSERT INTO t2 VALUES (1, 'abc');
    INSERT INTO t2 VALUES (2, 'org');
    INSERT INTO t2 VALUES (3, 'omg');
    INSERT INTO t2 VALUES (4, 'osk');
    GO
    
    INSERT INTO t1 ([name])
    SELECT [name] FROM t2;
    GO
    
    SELECT * FROM t1;
    GO
    
    DROP TABLE t1;
    DROP TABLE t2;
    GO

    就是你要的結果。你可以上下兩段寫在一起,並且在 t1 的 id 欄位加上 PRIMARY KEY 約束條件,結果應該是一樣的。
    • 已標示為解答 Evan1002 2009年11月30日 下午 01:47
    2009年11月29日 下午 07:10

所有回覆

  • hi
    很正常
    因為你的 A資料庫T1已經有重複ID了,所以會出現錯誤訊息

    先truncate talbe dbo.t1
    在執行就可以了
    SET IDENTITY_INSERT dbo.t1 ON
    INSERT INTO dbo.t1(t1.ID,t1.name)
    SELECT t2.ID, t2.Name FROM dbo.t2
    go


    http://www.dotblogs.com.tw/ricochen/Default.aspx
    2009年11月29日 下午 03:46
    版主
  • hi ricoisme,

    非常感謝您的提供,不好意思,忘了說明 A資料庫的t1(id) 有外部索引鍵的關聯,

    delete 跟 Truncate我有試過,會出現 "無法截斷,因為FK條件約束所參考"

    想請教您或各位前輩:

    1.依上述狀況,有辦法insert 過去A資料庫的t1時,讓資料行id下面的值自動增加3,4,5,6 即:

    id     name
    --------------
    1        abc
    2        org
    3        abc
    4        org
    5        omg
    4        osk


    2.或者是將FK暫時停用,然後將表格delete or truncate後再恢復FK呢?

    以上請教各位前輩! 非常感謝!!
    2009年11月29日 下午 03:54
  • 您是不是誤解了 SET IDENTITY_INSERT 陳述式的功能?
    線上叢書寫得很清楚:允許將明確的值插入資料表的識別資料行中。不是你說的:將識別碼啟用,讓 id 的數值自動成長。

    要達到你說的效果,修正一下應該就 OK 了吧...

    --set identity_insert A..t1 on; 
    insert into a..t1 ([name])
    select [name] from b..t2;
    --set identity_insert A..t1 off;
    

    ---
    SQL 2005 線上叢書 - SET IDENTITY_INSERT (Transact-SQL)
    • 已編輯 hunterpo0323 2009年11月29日 下午 04:46 補充參考連結
    2009年11月29日 下午 04:40
  • hi hunterpro0323,

    非常感謝您的回覆,因為我利用圖形化介面操作時候,識別碼啟用(是) and (1,1) 這樣的話從資料庫B的T2複製的資料列貼到T1,id的數值是會

    增加的,當然關掉後就不會增加,也就會出現插入重複索引鍵的錯誤訊息,只是用指令下的時候,就會出現該錯誤訊息,不好意思,不知道圖形化的

    識別碼跟語法的identity一不一樣,感覺上應該是一樣的,不好意思,對此部是很熟悉,感謝您提供參考!

    另您所提供修正的:

    insert into a..t1 ([name])
    select [name] from b..t2

    如果只有這樣輸入,因為資料表id已經設定了PK,因此一定要開啟識別碼(set identity_insert on),沒這樣做的話錯誤訊息會顯示要開啟。

    不知道各位前輩是否有好的方式呢?

    2009年11月29日 下午 06:00
  • 另您所提供修正的:

    insert into a..t1 ([name])
    select [name] from b..t2

    如果只有這樣輸入,因為資料表id已經設定了PK,因此一定要開啟識別碼(set identity_insert on),沒這樣做的話錯誤訊息會顯示要開啟。

    不知道各位前輩是否有好的方式呢?

    似乎不太對...我反覆試了幾種情況,沒出現你說的「因為資料表 id 已經設定了 PK,因此一定要 set identity_insert on」,應該這麼說,
    SET IDENTITY_INSERT ON 只有在 id 設為識別值,並且你要自行指定一個值給它,跟是否為 PK 無關,例如:

    USE tempdb;
    GO
    
    CREATE TABLE t1
    (
    	id INT IDENTITY NOT NULL,
    	name NVarChar(20)
    )
    GO
    
    SET IDENTITY_INSERT dbo.t1 ON;
    INSERT INTO t1 (id, name) VALUES (1, 'abc');
    INSERT INTO t1 (id, name) VALUES (2, 'org');
    SET IDENTITY_INSERT dbo.t1 OFF;
    GO

    所以我判斷你在 t1 的 id 欄位有設定 IDENTITY 屬性。接下來你可以在執行以下語法:

    USE tempdb;
    GO
    
    CREATE TABLE t2
    (
    	id INT NOT NULL,
    	name NVarChar(20)
    )
    GO
    
    INSERT INTO t2 VALUES (1, 'abc');
    INSERT INTO t2 VALUES (2, 'org');
    INSERT INTO t2 VALUES (3, 'omg');
    INSERT INTO t2 VALUES (4, 'osk');
    GO
    
    INSERT INTO t1 ([name])
    SELECT [name] FROM t2;
    GO
    
    SELECT * FROM t1;
    GO
    
    DROP TABLE t1;
    DROP TABLE t2;
    GO

    就是你要的結果。你可以上下兩段寫在一起,並且在 t1 的 id 欄位加上 PRIMARY KEY 約束條件,結果應該是一樣的。
    • 已標示為解答 Evan1002 2009年11月30日 下午 01:47
    2009年11月29日 下午 07:10
  • hi ricoisme,

    非常感謝您的提供,不好意思,忘了說明 A資料庫的t1(id) 有外部索引鍵的關聯,

    delete 跟 Truncate我有試過,會出現 "無法截斷,因為FK條件約束所參考"

    想請教您或各位前輩:

    1.依上述狀況,有辦法insert 過去A資料庫的t1時,讓資料行id下面的值自動增加3,4,5,6 即:

    id     name
    --------------
    1        abc
    2        org
    3        abc
    4        org
    5        omg
    4        osk


    2.或者是將FK暫時停用,然後將表格delete or truncate後再恢復FK呢?

    以上請教各位前輩! 非常感謝!!

    1.可以參考hunterpo0323
    2.可參考
    How to: Disable Foreign Key Constraints with INSERT and UPDATE Statements (Visual Database Tools)
    http://technet.microsoft.com/en-us/library/ms175041.aspx
    ALTER TABLE (Transact-SQL)
    http://msdn.microsoft.com/zh-tw/library/ms190273(SQL.90).aspx

    http://www.dotblogs.com.tw/ricochen/Default.aspx
    2009年11月30日 上午 12:01
    版主