SQL Server 跨資料庫複製資料表：SELECT INTO、INSERT SELECT 與身分識別欄位陷阱

情境：本地表 hw2 → 遠端資料庫 hw（同結構、已有 2 萬筆）

方法一：SELECT INTO 建立新表

SELECT * INTO hw3 FROM dbo.hw2;

• 自動建立 hw3，結構與資料一次完成
• 不複製索引、約束、觸發器、權限
• 目標表不可存在

方法二：INSERT SELECT 追加資料（核心技巧）

INSERT INTO hw (
    sort3_id, sort2_id, sort_id, hw_name, hw_cash, hw_danwei, hw_content,
    hw_date, isbn, hw_oldcash, admintuijian, tejia, hw_kucun, ZRXS,
    hw_name2, hw_oldcash2, baozhuang, baozhuang2, guige, wail, waiw,
    waih, zhuangxiang, neihe, chushoutype, hw_content2, hw_danwei2,
    tuijianzhishu, pifa, factory_code, pro_code, cqzl, cqmz, tjxs,
    pp, beizhu, hits
)
SELECT
    sort3_id, sort2_id, sort_id, hw_name, hw_cash, hw_danwei, hw_content,
    hw_date, isbn, hw_oldcash, admintuijian, tejia, hw_kucun, ZRXS,
    hw_name2, hw_oldcash2, baozhuang, baozhuang2, guige, wail, waiw,
    waih, zhuangxiang, neihe, chushoutype, hw_content2, hw_danwei2,
    tuijianzhishu, pifa, factory_code, pro_code, cqzl, cqmz, tjxs,
    pp, beizhu, hits
FROM hw2;

致命陷阱：身分識別欄位 (IDENTITY)

「僅當使用了資料行清單，並且 IDENTITY_INSERT 為 ON 時，才能在表 'hw' 中為識別欄位指定顯式值。」

解決方案

1. 明確列出欄位清單（如上例），排除 IDENTITY 欄位，讓目標表自動產生新值
2. 若必須保留原 ID 值：

   SET IDENTITY_INSERT hw ON;
   INSERT INTO hw (ID, col1, col2...) SELECT ID, col1, col2... FROM hw2;
   SET IDENTITY_INSERT hw OFF;

   注意：同一時間僅允許一張表開啟 IDENTITY_INSERT ON，且需 db_ddladmin 權限。

跨伺服器複製（Linked Server）

-- 1. 建立連結伺服器
EXEC sp_addlinkedserver @server='RemoteSrv', @srvproduct='', @provider='SQLNCLI', @datasrc='192.168.1.100';
EXEC sp_addlinkedsrvlogin 'RemoteSrv', 'false', NULL, 'RemoteUser', 'RemotePwd';

-- 2. 四段式命名複製
INSERT INTO [RemoteSrv].[RemoteDB].[dbo].[hw] (col1, col2...)
SELECT col1, col2... FROM dbo.hw2;

大量資料優化建議

• 分批次（如每 5 萬筆）避免長交易鎖定與日誌暴漲
• 使用 BULK INSERT / BCP / SSIS 替代 INSERT SELECT
• 暫時停用目標表非叢集索引，匯入後重建
• 簡單復原模式下操作減少日誌