[MSSQL_2016] 如何insert大量資料至已建立pk的表中

Hits: 750

緣起

在先前的文章中提到，目前在處理的工作是把server_A中幾十億列的資料，分成2017年1月1日以前與以後的資料，將舊資料備份至server_B，並且刪除server_A中的舊資料。同事已經把schema(含複合主鍵 composite / compond PK)在server_B中建好了。

失敗的解法1

收到這個任務，我採用最直覺的作法，就是直接把server_A的資料篩選後寫入server_B中：

select [server_A].table into [server_B].table where date < '2017-01-01'

--error: 666
--索引已超出系統能為重複群組產生的唯一值上限。卸除並重新建立索引或許能解決這個問題; 否則請使用另一個叢集索引鍵"

把這個錯誤訊息跟主管討論後，主管根據這篇文章判斷可能是因為server_B中的其中一個複合主鍵欄位(int型態)超過int型態的最大長度(2^31約等於21億)，因此要換個方法作。

成功的解法2

後來我就思考，如果我先把server_B中的複合主鍵欄位拿掉，待insert into完成後(執行一次需30小時左右Orz)，再把pk建立起來，也成功的解掉這個問題了。至於詳細原因還要再搜尋一下，待搜尋完成後再來更新。

建立複合主鍵的指令

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.[your_table_name] ADD CONSTRAINT
    [your_pk_name] PRIMARY KEY NONCLUSTERED 
    (
    COLUMN1,
    COLUMN2,
    COLUMN3,
    COLUMN4
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
ALTER TABLE dbo.[your_table_name] SET (LOCK_ESCALATION = TABLE)
GO
COMMIT