Sql Server 2005 - 如果不存在则插入
互联网上有很多关于这个常见问题"的信息.
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 需要读取以检查唯一性
- 在此处查看我的答案:仅插入如果它不存在则为一行 和 SQL Server 2008:INSERT 如果不退出，保持唯一列
- 如果您也需要更新:delete-insert 与 if-update else-insert 中哪个是最佳选择?
如果您可以丢弃重复项，这是一种高度可扩展的技术
If you can discard duplicates, this is a highly scalable technique
链接:
相关文章