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

點閱: 250

緣起

先前的文章中提到,目前在處理的工作是把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

About the Author

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

Related Posts