如果想要一次更新或插入所有值而不仅仅是一个记录,我使用了以下代码:
首先运行更新脚本
UPDATE Table1
SET OPIS = T1.OPIS
FROM
Table1 AS T
INNER JOIN
Table2 AS T1
ON
T.col = T1.col;
然后执行插入脚本
INSERT INTO Table1
SELECT * FROM
(
SELECT T1.* Table2 AS T1
LEFT JOIN Table1 AS T2 ON (T2.col = T1.col)
WHERE T2.col IS NULL
) AS T;
希望有人会发现这个有用。
在MySQL中,这个的等效方法(在某些情况下)可能如下:
INSERT INTO table (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE c=c+1;
有人可能会认为这与Solutions for INSERT OR UPDATE on SQL Server相关。
使用MERGE (Transact-SQL)更新版本:
DECLARE @USER_ID AS INT=76;
DECLARE @TYPE AS NVARCHAR(MAX)='set.global';
DECLARE @FKEY AS NVARCHAR(MAX)='21';
DECLARE @DATA AS NVARCHAR(MAX)='test';
begin tran
MERGE UserData
USING (SELECT @USER_ID, @TYPE, @FKEY, @DATA) AS Source([UserId], [Type], [FKey], [Data])
ON (UserData.[UserId] = Source.[UserId] AND UserData.[Type] = Source.[Type] AND (UserData.[FKey] = Source.[FKey] OR (Source.[FKey] IS NULL AND UserData.[FKey] IS NULL)))
WHEN MATCHED
THEN
UPDATE SET [Data] = Source.[Data]
WHEN NOT MATCHED BY TARGET THEN
INSERT
([UserId]
,[Type]
,[FKey]
,[Data])
VALUES
( Source.[UserId]
,Source.[Type]
,Source.[FKey]
,Source.[Data]);
commit tran