<i id='v3JuT'><tr id='v3JuT'><dt id='v3JuT'><q id='v3JuT'><span id='v3JuT'><b id='v3JuT'><form id='v3JuT'><ins id='v3JuT'></ins><ul id='v3JuT'></ul><sub id='v3JuT'></sub></form><legend id='v3JuT'></legend><bdo id='v3JuT'><pre id='v3JuT'><center id='v3JuT'></center></pre></bdo></b><th id='v3JuT'></th></span></q></dt></tr></i><div id='v3JuT'><tfoot id='v3JuT'></tfoot><dl id='v3JuT'><fieldset id='v3JuT'></fieldset></dl></div>

      <small id='v3JuT'></small><noframes id='v3JuT'>

        <tfoot id='v3JuT'></tfoot>
      1. <legend id='v3JuT'><style id='v3JuT'><dir id='v3JuT'><q id='v3JuT'></q></dir></style></legend>

        • <bdo id='v3JuT'></bdo><ul id='v3JuT'></ul>

        SQL Server 上 INSERT OR UPDATE 的解决方案

        时间:2023-07-18

        <i id='6Q32N'><tr id='6Q32N'><dt id='6Q32N'><q id='6Q32N'><span id='6Q32N'><b id='6Q32N'><form id='6Q32N'><ins id='6Q32N'></ins><ul id='6Q32N'></ul><sub id='6Q32N'></sub></form><legend id='6Q32N'></legend><bdo id='6Q32N'><pre id='6Q32N'><center id='6Q32N'></center></pre></bdo></b><th id='6Q32N'></th></span></q></dt></tr></i><div id='6Q32N'><tfoot id='6Q32N'></tfoot><dl id='6Q32N'><fieldset id='6Q32N'></fieldset></dl></div>

      2. <tfoot id='6Q32N'></tfoot>
        • <bdo id='6Q32N'></bdo><ul id='6Q32N'></ul>

            <legend id='6Q32N'><style id='6Q32N'><dir id='6Q32N'><q id='6Q32N'></q></dir></style></legend>
              <tbody id='6Q32N'></tbody>

                  <small id='6Q32N'></small><noframes id='6Q32N'>

                • 本文介绍了SQL Server 上 INSERT OR UPDATE 的解决方案的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

                  问题描述

                  假设表结构为MyTable(KEY, datafield1, datafield2...).

                  我经常想更新现有记录,或者在不存在的情况下插入新记录.

                  Often I want to either update an existing record, or insert a new record if it doesn't exist.

                  本质上:

                  IF (key exists)
                    run update command
                  ELSE
                    run insert command
                  

                  最好的编写方式是什么?

                  What's the best performing way to write this?

                  推荐答案

                  不要忘记事务.性能不错,但简单(IF EXISTS..)方法非常危险.
                  当多个线程将尝试执行插入或更新时,您可以轻松地获取主键违规.

                  don't forget about transactions. Performance is good, but simple (IF EXISTS..) approach is very dangerous.
                  When multiple threads will try to perform Insert-or-update you can easily get primary key violation.

                  @Beau Crawford & 提供的解决方案@Esteban 展示了总体思路,但容易出错.

                  Solutions provided by @Beau Crawford & @Esteban show general idea but error-prone.

                  为了避免死锁和PK违规,您可以使用以下内容:

                  To avoid deadlocks and PK violations you can use something like this:

                  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
                  

                  begin tran
                     update table with (serializable) set ...
                     where key = @key
                  
                     if @@rowcount = 0
                     begin
                        insert into table (key, ...) values (@key,..)
                     end
                  commit tran
                  

                  这篇关于SQL Server 上 INSERT OR UPDATE 的解决方案的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

                  上一篇:基于 ID 匹配的从一个表到另一个表的 SQL 更新 下一篇:SQLSERVER 中的 ListAGG

                  相关文章

                  • <bdo id='jilYr'></bdo><ul id='jilYr'></ul>

                  <legend id='jilYr'><style id='jilYr'><dir id='jilYr'><q id='jilYr'></q></dir></style></legend>

                  <i id='jilYr'><tr id='jilYr'><dt id='jilYr'><q id='jilYr'><span id='jilYr'><b id='jilYr'><form id='jilYr'><ins id='jilYr'></ins><ul id='jilYr'></ul><sub id='jilYr'></sub></form><legend id='jilYr'></legend><bdo id='jilYr'><pre id='jilYr'><center id='jilYr'></center></pre></bdo></b><th id='jilYr'></th></span></q></dt></tr></i><div id='jilYr'><tfoot id='jilYr'></tfoot><dl id='jilYr'><fieldset id='jilYr'></fieldset></dl></div>

                  1. <small id='jilYr'></small><noframes id='jilYr'>

                    <tfoot id='jilYr'></tfoot>