检查是否存在行,否则插入

我需要编写一个T-SQL存储过程来更新表中的行.如果该行不存在,请插入它.所有这些步骤都由事务包装.

这是一个预订系统,所以它必须是原子和可靠的.如果交易已提交且预订航班,则必须返回true.

我是T-SQL的新手,不知道如何使用@@rowcount.这是我到现在为止所写的.我在正确的道路上吗？我确信这对你来说很容易.

-- BEGIN TRANSACTION (HOW TO DO?)

UPDATE Bookings
 SET TicketsBooked = TicketsBooked + @TicketsToBook
 WHERE FlightId = @Id AND TicketsMax < (TicketsBooked + @TicketsToBook)

-- Here I need to insert only if the row doesn't exists.
-- If the row exists but the condition TicketsMax is violated, I must not insert 
-- the row and return FALSE

IF @@ROWCOUNT = 0 
BEGIN

 INSERT INTO Bookings ... (omitted)

END

-- END TRANSACTION (HOW TO DO?)

-- Return TRUE (How to do?)

dance2die.. 154

看看MERGE命令.你可以做UPDATE,INSERT并DELETE在一个声明中.

这是一个使用的工作实现MERGE
- 它在进行更新之前检查航班是否已满,否则进行插入.

if exists(select 1 from INFORMATION_SCHEMA.TABLES T 
              where T.TABLE_NAME = 'Bookings') 
begin
    drop table Bookings
end
GO

create table Bookings(
  FlightID    int identity(1, 1) primary key,
  TicketsMax    int not null,
  TicketsBooked int not null
)
GO

insert  Bookings(TicketsMax, TicketsBooked) select 1, 0
insert  Bookings(TicketsMax, TicketsBooked) select 2, 2
insert  Bookings(TicketsMax, TicketsBooked) select 3, 1
GO

select * from Bookings

然后 ...

declare @FlightID int = 1
declare @TicketsToBook int = 2

--; This should add a new record
merge Bookings as T
using (select @FlightID as FlightID, @TicketsToBook as TicketsToBook) as S
    on  T.FlightID = S.FlightID
      and T.TicketsMax > (T.TicketsBooked + S.TicketsToBook)
  when matched then
    update set T.TicketsBooked = T.TicketsBooked + S.TicketsToBook
  when not matched then
    insert (TicketsMax, TicketsBooked) 
    values(S.TicketsToBook, S.TicketsToBook);

select * from Bookings

另外,请查看为什么您可能希望[MER(HOLDLOCK)](http://weblogs.sqlteam.com/dang/archive/2009/01/31/UPSERT-Race-Condition-With-MERGE.aspx)获得该MERGE. (5认同)

我认为MERGE在2005年之后得到支持(所以2008年以上). (4认同)

没有WITH(UPDLOCK)的MERGE可能会发生主键冲突,在这种情况下会很糟糕.请参阅[MERGE是SQL2008中的原子语句吗？](http://stackoverflow.com/questions/9871644/is-merge-an-atomic-statement-in-sql2008) (3认同)

---

Gregory A Be.. 150

我假设每个航班都有一排？如果是这样:

IF EXISTS (SELECT * FROM Bookings WHERE FLightID = @Id)
BEGIN
    --UPDATE HERE
END
ELSE
BEGIN
   -- INSERT HERE
END

我假设我所说的,因为你做事的方式可以超额预订航班,因为当最多10张票并且你预订20时它会插入一个新行.

1> dance2die..：

---

看看MERGE命令.你可以做UPDATE,INSERT并DELETE在一个声明中.

这是一个使用的工作实现MERGE
- 它在进行更新之前检查航班是否已满,否则进行插入.

if exists(select 1 from INFORMATION_SCHEMA.TABLES T 
              where T.TABLE_NAME = 'Bookings') 
begin
    drop table Bookings
end
GO

create table Bookings(
  FlightID    int identity(1, 1) primary key,
  TicketsMax    int not null,
  TicketsBooked int not null
)
GO

insert  Bookings(TicketsMax, TicketsBooked) select 1, 0
insert  Bookings(TicketsMax, TicketsBooked) select 2, 2
insert  Bookings(TicketsMax, TicketsBooked) select 3, 1
GO

select * from Bookings

然后 ...

declare @FlightID int = 1
declare @TicketsToBook int = 2

--; This should add a new record
merge Bookings as T
using (select @FlightID as FlightID, @TicketsToBook as TicketsToBook) as S
    on  T.FlightID = S.FlightID
      and T.TicketsMax > (T.TicketsBooked + S.TicketsToBook)
  when matched then
    update set T.TicketsBooked = T.TicketsBooked + S.TicketsToBook
  when not matched then
    insert (TicketsMax, TicketsBooked) 
    values(S.TicketsToBook, S.TicketsToBook);

select * from Bookings

---

另外,请查看为什么您可能希望[MER(HOLDLOCK)](http://weblogs.sqlteam.com/dang/archive/2009/01/31/UPSERT-Race-Condition-With-MERGE.aspx)获得该MERGE.

---

我认为MERGE在2005年之后得到支持(所以2008年以上).

---

没有WITH(UPDLOCK)的MERGE可能会发生主键冲突,在这种情况下会很糟糕.请参阅[MERGE是SQL2008中的原子语句吗？](http://stackoverflow.com/questions/9871644/is-merge-an-atomic-statement-in-sql2008)

2> Gregory A Be..：

---

我假设每个航班都有一排？如果是这样:

IF EXISTS (SELECT * FROM Bookings WHERE FLightID = @Id)
BEGIN
    --UPDATE HERE
END
ELSE
BEGIN
   -- INSERT HERE
END

我假设我所说的,因为你做事的方式可以超额预订航班,因为当最多10张票并且你预订20时它会插入一个新行.

---

@GregoryABeamer - 简单地将它放在默认隔离级别的`BEGIN TRAN ... COMMIT`中将无法解决问题.OP规定**原子和可靠**是要求.您的答案无法以任何形式或形式解决此问题.

---

如果将(UPDLOCK,HOLDLOCK)添加到SELECT中,这是否是线程安全的:`IF EXISTS(SELECT*FROM Bookings(UPDLOCK,HOLDLOCK)WHERE FLightID = @Id)`？

3> Cassius Porc..：

---

在测试行是否存在时,传递updlock,rowlock,holdlock提示.

begin tran /* default read committed isolation level is fine */

if not exists (select * from Table with (updlock, rowlock, holdlock) where ...)
    /* insert */
else
    /* update */

commit /* locks are released here */

updlock提示会强制查询对该行进行更新锁定(如果已存在),从而阻止其他事务在您提交或回滚之前对其进行修改.

holdlock提示会强制查询执行范围锁定,从而阻止其他事务添加与筛选条件匹配的行,直到您提交或回滚为止.

行锁提示强制将粒度锁定到行级而不是默认页级,因此您的事务不会阻止尝试更新同一页中不相关行的其他事务(但要注意减少争用和增加之间的权衡)锁定开销 - 您应该避免在单个事务中占用大量行级锁定.

有关更多信息,请参见http://msdn.microsoft.com/en-us/library/ms187373.aspx.

请注意,锁被视为执行它们的语句 - 调用begin tran不会让你免受另一个事务的影响,在你到达它之前对某些事情进行锁定.您应该尝试通过尽快提交事务(获取较晚,尽早发布)来使SQL在最短的时间内保持锁定.

请注意,如果PK是bigint,则行级锁可能效果较差,因为SQL Server上的内部哈希值对于64位值是简并的(不同的键值可能会散列到相同的锁定ID).

---

锁定对于避免超量预订非常重要.假设IF语句中声明的锁保持到IF语句结束,即一个更新语句,这是正确的吗？然后,使用开始结束块标记显示上面的代码可能是明智的,以防止新手复制和粘贴代码并仍然出错.

4> 小智..：

---

我在写我的解决方案.我的方法不能代表'if'或'merge'.我的方法很简单.

INSERT INTO TableName (col1,col2)
SELECT @par1, @par2
   WHERE NOT EXISTS (SELECT col1,col2 FROM TableName
                     WHERE col1=@par1 AND col2=@par2)

例如:

INSERT INTO Members (username)
SELECT 'Cem'
   WHERE NOT EXISTS (SELECT username FROM Members
                     WHERE username='Cem')

说明:

(1)SELECT col1,col2 FROM TableName WHERE col1 = @ par1 AND col2 = @ par2它从TableName中选择搜索值

(2)SELECT @ par1,@ par2 WHERE NOT EXISTS如果不存在(1)子查询则需要

(3)插入TableName(2)步骤值