SQL Server 手動插入識別欄值：SET IDENTITY_INSERT 開關完整指南

報錯場景

INSERT INTO member (id, username) VALUES (10, 'admin');
-- Msg 544, Level 16, State 1: 
-- Cannot insert explicit value for identity column in table 'member' when IDENTITY_INSERT is set to OFF.

解決方案：臨時開啟 IDENTITY_INSERT

SET IDENTITY_INSERT member ON;      -- 開啟
INSERT INTO member (id, username) VALUES (10, 'admin');  -- 必須顯式列出欄位
SET IDENTITY_INSERT member OFF;     -- 立即關閉

⚠️ 必須遵守的規則

1. 同一會話同一時間，資料庫內僅允許一張表開啟 IDENTITY_INSERT ON
2. INSERT 必須顯式指定欄位清單，不能用 INSERT INTO table VALUES (...) 省略欄位
3. 插入值可任意（不受種子/增量限制），甚至可填補刪除產生的間隙
4. 完成後立即關閉，避免阻塞其他操作

SELECT OBJECTPROPERTY(OBJECT_ID('member'), 'TableHasIdentity') AS HasIdentity,
       COLUMNPROPERTY(OBJECT_ID('member'), 'id', 'IsIdentity') AS IsIdentityCol;