INSERT or UPDATE 或者有人稱 Upsert，是一個撰寫資料庫存取應用程式一定會遇到的場景，這個看起來簡單不過的寫入資料邏輯，到了實際真正要施作的時候，是有一些細節需要去注意的，下面我就我所知道的三種 INSERT or UPDATE 的寫法跟各位朋友分享。
我建立了一個 KeyValue 的資料表，欄位有兩個，分別為 Key 及 Value，我們就拿它來當成實驗的對象。
第一種：存在就更新，不存在就新增。
第一種可能是我們優先會想到寫法，先檢查資料存不存在？如果存在就更新，不存在就新增，寫法大致上就像下面這樣：
IF EXISTS (SELECT
1
FROM KeyValue kv
WHERE kv.[Key] = @Key)
BEGIN
UPDATE KeyValue
SET [Value] = @Value
WHERE [Key] = @Key;
END
ELSE
BEGIN
INSERT INTO KeyValue([Key]
,[Value])
VALUES (@Key
,@Value);
END
但是當使用者同時送了兩筆相同主鍵資料進來的時候，這樣的寫法可能會遇到一個例外錯誤：
Violation of PRIMARY KEY constraint 'PK_XXX'. Cannot insert duplicate key in object 'dbo.YYY'. The duplicate key value is (ZZZ).
The statement has been terminated.
這個我們透過將整段 SQL 語句封裝在一個交易裡面，並且設定隔離層級是 SERIALIZABLE 表示交易結束才將鎖定的資源解鎖，然後在 KeyValue 資料表上指定鎖定範圍是列鎖定（ROWLOCK），避免鎖定範圍被 SQL Server 升級，這樣做之後，例外錯誤就不再出現了。
BEGIN TRANSACTION
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
IF EXISTS (SELECT
1
FROM KeyValue kv WITH (ROWLOCK)
WHERE kv.[Key] = @Key)
BEGIN
UPDATE KeyValue
SET [Value] = @Value
WHERE [Key] = @Key;
END
ELSE
BEGIN
INSERT INTO KeyValue([Key]
,[Value])
VALUES (@Key
,@Value);
END
COMMIT TRANSACTION
如果是用 ADO.NET 來存取資料庫的話，則可以透過呼叫 BeginTransaction() 方法，傳入 System.Data.IsolationLevel.Serializable 參數來封裝 SQL 語句。
var sql = @"
INSERT INTO KeyValue([Key]
,[Value])
SELECT
@Key
,@Value
WHERE NOT EXISTS (SELECT
1
FROM KeyValue kv WITH (ROWLOCK)
WHERE kv.[Key] = @Key);
IF @@rowcount = 0
BEGIN
UPDATE KeyValue
SET [Value] = @Value
WHERE [Key] = @Key;
END";
using (var db = new SqlConnection("ConnectionString"))
{
db.Open();
using (var tx = db.BeginTransaction(System.Data.IsolationLevel.Serializable))
{
try
{
// 此處需要引用 Dapper
db.Execute(sql, new { Key = key, Value = value }, transaction: tx);
tx.Commit();
}
catch
{
tx.Rollback();
throw;
}
}
}
第二種：更新不到資料，就新增。
再來第二種我們是先執行更新，如果沒有任何資料被異動，才執行新增，跟第一種一樣，交易、隔離層級、鎖定的提示都不能少，寫法大致如下：
BEGIN TRANSACTION
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
UPDATE KeyValue WITH (ROWLOCK)
SET [Value] = @Value
WHERE [Key] = @Key;
IF @@rowcount = 0
BEGIN
INSERT INTO KeyValue([Key]
,[Value])
VALUES (@Key
,@Value);
END
COMMIT TRANSACTION
這種寫法利用了執行 SQL 語句會回傳受到影響的資料列的特性，當執行更新之後，如果受到影響的資料列為 0 時，表示說資料不存在，這個時候我們才去執行新增。
第三種：新增不到資料，就更新。
最後一種則是將第二種的邏輯反過來，先執行新增，如果沒有任何資料被異動，才執行更新。
BEGIN TRANSACTION
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
INSERT INTO KeyValue([Key]
,[Value])
SELECT
@Key
,@Value
WHERE NOT EXISTS (SELECT
1
FROM KeyValue kv WITH (ROWLOCK)
WHERE kv.[Key] = @Key);
IF @@rowcount = 0
BEGIN
UPDATE KeyValue
SET [Value] = @Value
WHERE [Key] = @Key;
END
COMMIT TRANSACTION
就差那麼一點點
在研究的過程當中，我一直朝向一個 SQL 語句能夠解決的目標在前進，最終搭配 OUTPUT 語法寫出了下面這個在編輯器上沒有語法錯誤的寫法：
INSERT INTO KeyValue([Key]
,[Value])
SELECT
@Key
,@Value
WHERE NOT EXISTS (SELECT
1
FROM (UPDATE KeyValue
SET [Value] = @Value
OUTPUT [INSERTED].*
WHERE [Key] = @Key) upd)
可是一執行就出現了下面的錯誤訊息，感覺邏輯上能通，但實際上 SQL Server 不允許。
A nested INSERT, UPDATE, DELETE, or MERGE statement is not allowed in a SELECT statement that is not the immediate source of rows for an INSERT statement.
為什麼不用 MERGE？
我個人是把 MERGE 定調在批次大量的資料異動時所使用的，單筆或小量的資料要增刪改，我不會用它，再來就是 MERGE 有一些 Issues 存在，各位朋友就自行前往查看，使用前最好要經過測試。
最後，如果大家還有其他種 INSERT or UPDATE 的寫法，還請不吝分享。
