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

我需要编写一个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?)
587836 次浏览
小开

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

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

我认为我说的，因为你的做事方式可能会超额预订航班，因为当最多有10张票而你预订了20张票时，它会插入一个新的行。

小开

这是我最近不得不做的事情:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[cjso_UpdateCustomerLogin]
(
@CustomerID AS INT,
@UserName AS VARCHAR(25),
@Password AS BINARY(16)
)
AS
BEGIN
IF ISNULL((SELECT CustomerID FROM tblOnline_CustomerAccount WHERE CustomerID = @CustomerID), 0) = 0
BEGIN
INSERT INTO [tblOnline_CustomerAccount] (
[CustomerID],
[UserName],
[Password],
[LastLogin]
) VALUES (
/* CustomerID - int */ @CustomerID,
/* UserName - varchar(25) */ @UserName,
/* Password - binary(16) */ @Password,
/* LastLogin - datetime */ NULL )
END
ELSE
BEGIN
UPDATE  [tblOnline_CustomerAccount]
SET     UserName = @UserName,
Password = @Password
WHERE   CustomerID = @CustomerID
END


END
小开

你可以使用合并功能来实现。否则你可以:

declare @rowCount int


select @rowCount=@@RowCount


if @rowCount=0
begin
--insert....
小开

看一下<强> < /合并强>命令。你可以做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
小开

在测试行是否存在时，传递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提示强制查询接受范围锁，防止其他事务添加匹配您的筛选条件的行，直到您提交或回滚。

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

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

注意，锁是在执行执行锁的语句时被接受的——调用begin tran并不能让你免受另一个事务的影响，在你到达某个事物之前就对它进行锁定。您应该通过尽可能快地提交事务(晚获取，早释放)来尝试将SQL分解为尽可能短的锁持有时间。

请注意，如果PK是bigint，行级锁可能不太有效，因为SQL Server上的内部哈希对于64位值是退化的(不同的键值可能哈希到相同的锁id)。

小开

我在写我的解。我的方法不支持“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不存在 if not exists from (1) subquery

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

小开

完整解决方案如下(包括游标结构)。非常感谢Cassius Porcus从上面发布的begin trans ... commit代码。

declare @mystat6 bigint
declare @mystat6p varchar(50)
declare @mystat6b bigint


DECLARE mycur1 CURSOR for


select result1,picture,bittot from  all_Tempnogos2results11


OPEN mycur1


FETCH NEXT FROM mycur1 INTO @mystat6, @mystat6p , @mystat6b


WHILE @@Fetch_Status = 0
BEGIN


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


if not exists (select * from all_Tempnogos2results11_uniq with (updlock, rowlock, holdlock)
where all_Tempnogos2results11_uniq.result1 = @mystat6
and all_Tempnogos2results11_uniq.bittot = @mystat6b )
insert all_Tempnogos2results11_uniq values (@mystat6 , @mystat6p , @mystat6b)


--else
--  /* update */


commit /* locks are released here */


FETCH NEXT FROM mycur1 INTO @mystat6 , @mystat6p , @mystat6b


END


CLOSE mycur1


DEALLOCATE mycur1
go
小开

我最终能够插入一行，在它不存在的情况下，使用以下模型:

INSERT INTO table ( column1, column2, column3 )
(
SELECT $column1, $column2, $column3
WHERE NOT EXISTS (
SELECT 1
FROM table
WHERE column1 = $column1
AND column2 = $column2
AND column3 = $column3
)
)

我在:

http://www.postgresql.org/message-id/87hdow4ld1.fsf@stark.xeocode.com

小开 
INSERT INTO table ( column1, column2, column3 )
SELECT $column1, $column2, $column3
EXCEPT SELECT column1, column2, column3
FROM table
小开 
INSERT INTO [DatabaseName1].dbo.[TableName1] SELECT * FROM [DatabaseName2].dbo.[TableName2]
WHERE [YourPK] not in (select [YourPK] from [DatabaseName1].dbo.[TableName1])
小开

解决这个问题的最佳方法是首先使数据库列UNIQUE

ALTER TABLE table_name ADD UNIQUE KEY

THEN INSERT IGNORE INTO table_name，该值将不会被插入，如果它导致重复的键/已经存在于表中。

小开

简单的方法将数据从T1复制到T2，避免在T2中重复

--Insert a new record
INSERT INTO dbo.Table2(NoEtu, FirstName, LastName)
SELECT t1.NoEtuDos, t1.FName, t1.LName
FROM dbo.Table1 as t1
WHERE NOT EXISTS (SELECT (1) FROM dbo.Table2  AS t2
WHERE t1.FName = t2.FirstName
AND t1.LName = t2.LastName
AND t1.NoEtuDos = t2.NoEtu)

提交答案