假设MyTable的表结构(KEY, datafield1, datafield2…)
通常,我希望更新现有记录,或者插入不存在的新记录。
从本质上讲:
IF (key exists)
run update command
ELSE
run insert command
最好的写法是什么?
假设MyTable的表结构(KEY, datafield1, datafield2…)
通常,我希望更新现有记录,或者插入不存在的新记录。
从本质上讲:
IF (key exists)
run update command
ELSE
run insert command
最好的写法是什么?
当前回答
/*
CREATE TABLE ApplicationsDesSocietes (
id INT IDENTITY(0,1) NOT NULL,
applicationId INT NOT NULL,
societeId INT NOT NULL,
suppression BIT NULL,
CONSTRAINT PK_APPLICATIONSDESSOCIETES PRIMARY KEY (id)
)
GO
--*/
DECLARE @applicationId INT = 81, @societeId INT = 43, @suppression BIT = 0
MERGE dbo.ApplicationsDesSocietes WITH (HOLDLOCK) AS target
--set the SOURCE table one row
USING (VALUES (@applicationId, @societeId, @suppression))
AS source (applicationId, societeId, suppression)
--here goes the ON join condition
ON target.applicationId = source.applicationId and target.societeId = source.societeId
WHEN MATCHED THEN
UPDATE
--place your list of SET here
SET target.suppression = source.suppression
WHEN NOT MATCHED THEN
--insert a new line with the SOURCE table one row
INSERT (applicationId, societeId, suppression)
VALUES (source.applicationId, source.societeId, source.suppression);
GO
用您需要的任何东西替换表和字段名。 注意使用状态。 然后在DECLARE行中为变量设置适当的值(和类型)。
欢呼。
其他回答
做一个if存在…其他的……至少要做两个请求(一个检查,一个采取行动)。下面的方法在记录存在时只需要一个,如果需要插入则需要两个:
DECLARE @RowExists bit
SET @RowExists = 0
UPDATE MyTable SET DataField1 = 'xxx', @RowExists = 1 WHERE Key = 123
IF @RowExists = 0
INSERT INTO MyTable (Key, DataField1) VALUES (123, 'xxx')
这取决于使用模式。一个人必须在不迷失在细节的情况下看到使用的大局观。例如,如果使用模式是在创建记录后99%更新,那么'UPSERT'是最佳解决方案。
在第一次插入(hit)之后,它将全部是单个语句更新,没有if或but。插入的'where'条件是必要的,否则它将插入重复的数据,并且您不希望处理锁定。
UPDATE <tableName> SET <field>=@field WHERE key=@key;
IF @@ROWCOUNT = 0
BEGIN
INSERT INTO <tableName> (field)
SELECT @field
WHERE NOT EXISTS (select * from tableName where key = @key);
END
MS SQL Server 2008引入了MERGE语句,我认为它是SQL:2003标准的一部分。正如许多人所表明的那样,处理一行情况并不是什么大问题,但当处理大型数据集时,就需要游标,随之而来的是所有性能问题。MERGE语句在处理大型数据集时将非常受欢迎。
不要忘记事务。性能很好,但是简单的方法(IF EXISTS..)非常危险。 当多个线程将尝试执行插入或更新时,您可以轻松地 获取主键冲突。
由@Beau Crawford和@Esteban提供的解决方案显示了大致的想法,但容易出错。
为了避免死锁和PK违规,你可以使用如下方法:
begin tran
if exists (select * from table with (updlock,serializable) where key = @key)
begin
update table set ...
where key = @key
end
else
begin
insert into table (key, ...)
values (@key, ...)
end
commit tran
or
begin tran
update table with (serializable) set ...
where key = @key
if @@rowcount = 0
begin
insert into table (key, ...) values (@key,..)
end
commit tran
Before everyone jumps to HOLDLOCK-s out of fear from these nafarious users running your sprocs directly :-) let me point out that you have to guarantee uniqueness of new PK-s by design (identity keys, sequence generators in Oracle, unique indexes for external ID-s, queries covered by indexes). That's the alpha and omega of the issue. If you don't have that, no HOLDLOCK-s of the universe are going to save you and if you do have that then you don't need anything beyond UPDLOCK on the first select (or to use update first).
scproc通常在非常受控的条件下运行,并假设有可信的调用者(中间层)。这意味着如果一个简单的upsert模式(更新+插入或合并)看到重复的PK,这意味着在中间层或表设计中存在错误,SQL将在这种情况下调用错误并拒绝记录。在这种情况下,放置HOLDLOCK除了降低性能外,还等于吃掉异常并接收潜在的错误数据。
话虽如此,使用MERGE或UPDATE然后INSERT在您的服务器上更容易,而且更容易出错,因为您不必记得在第一次选择时添加(UPDLOCK)。此外,如果您正在小批量地进行插入/更新,则需要了解数据,以便决定某个事务是否合适。如果它只是一个不相关记录的集合,那么额外的“包络”交易将是有害的。