Sql Server 2005 - 如果不存在则插入 [英] Sql Server 2005 - Insert if not exists

问题描述

互联网上有很多关于这个常见问题"的信息.

There is lots of information in the internet regarding this common "problem".

解决方案如:

IF NOT EXISTS() BEGIN INSERT INTO (...) END

在我看来不是线程安全的，你可能会同意.

are not thread-safe in my opinion and you will probably agree.

但是你能确认把exist放到一个单选的where子句中就可以解决sql引擎中最高并发的问题吗?够了吗?

However could you confirm that putting the exist into the where clause of one single select would solve the problem of the highest concurrency in sql engine? Is it enough?

insert into Table (columns)
select column1, column2, column3
where not exists (select top 1 1 from Table where something)

应该还添加一些更高的事务级别或这可以在默认的上执行吗:已提交?

Should be there also added some higher transaction level or can this be executed on a default one: committed?

这会在未提交的级别下工作吗?

Would this work under uncommitted level?

谢谢！

//稍后添加

我可以假设两个 sql' 都是正确的吗:

Can i assume that both sql' are correct:

1)设置事务隔离级别可重复读

1) set transaction isolation level repeatable read

   IF NOT EXISTS() BEGIN INSERT INTO (...) END

2) 设置事务隔离级别可重复读

2) set transaction isolation level repeatable read

insert into Table (columns)
select column1, column2, column3
where not exists (select top 1 1 from Table where something)

推荐答案

使用 TRY/CATCH 可以避免额外的读取

With TRY/CATCH you can avoid the extra read

BEGIN TRY
   INSERT etc
END TRY
BEGIN CATCH
    IF ERROR_NUMBER() <> 2627
      RAISERROR etc
END CATCH

• A NOT EXISTS 将读取表，无论是在 IF 还是 WHERE 中
• INSERT 需要读取以检查唯一性

如果您可以丢弃重复项，这是一种高度可扩展的技术

If you can discard duplicates, this is a highly scalable technique

链接:

• 在此处查看我的答案:仅插入如果它不存在则为一行 和 SQL Server 2008:INSERT 如果不退出，保持唯一列
• 如果您也需要更新:delete-insert 与 if-update else-insert 中哪个是最佳选择?

这篇关于Sql Server 2005 - 如果不存在则插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！