防止插入时出现重复行 [英] Preventing Duplicate Rows on Insert

问题描述

我有一个表使用标识列作为其主键和两个

列（为简单而减少表）EmployeeNumber和ArrivalTime。


CREATE TABLE [tblRecords]（

[ID] [bigint] IDENTITY（1,1）NOT NULL，

[EmployeeNumber] [varchar]（10）COLLATE SQL_Latin1_General_CP1_CI_AS

NOT NULL，

[ArrivalTime] [datetime] NOT NULL，

CONSTRAINT [PK_tblRecords] PRIMARY KEY CLUSTERED

（

[ID]

）ON [PRIMARY]

）ON [PRIMARY]

GO


我有一个插入程序，在插入之前检查重复项

a新记录：


IF（SELECT TOP 1 [ID] FROM tblRecords WHERE EmployeeNumber =

@SocialSecurity）IS NULL

BEGIN

INSERT INTO tblRecords（EmployeeNumber，ArrivalTime）

VALUES （@EmployeeNumber，@ ArrivalTime）

SELECT SCOPE_IDENTITY（）

END

ELSE

SELECT 0 AS DuplicateRecord


在99.9％的情况下，这很有效。但是，如果

插入尝试的字面意思是滴答。除此之外，SELECT TOP 1 ...

命令在第一次尝试完成之前完成两次尝试。

所以如果程序我最终会有重复的条目被称为多次

倍迅速。系统需要在过去45天内防止重复

EmployeeNumbers，因此将EmployeeNumber

设置为UNIQUE将不起作用。我可以很容易地查看较旧的条目（45天或

更新），但我不知道如何处理在毫秒内多次调用

过程的时间。在这种情况下使用ROLLBACK

进行INSERT后，是否会带有重复检查的

TRANSACTION？非常感谢任何帮助！


-E

I have a table using an identity column as its Primary Key and two
columns (table reduced for simplicity) EmployeeNumber and ArrivalTime.

CREATE TABLE [tblRecords] (
[ID] [bigint] IDENTITY (1, 1) NOT NULL ,
[EmployeeNumber] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[ArrivalTime] [datetime] NOT NULL ,
CONSTRAINT [PK_tblRecords] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
) ON [PRIMARY]
GO

I have an insert procedure that checks for duplicates before inserting
a new record:

IF (SELECT TOP 1 [ID] FROM tblRecords WHERE EmployeeNumber =
@SocialSecurity) IS NULL
BEGIN
INSERT INTO tblRecords(EmployeeNumber,ArrivalTime)
VALUES (@EmployeeNumber, @ArrivalTime)
SELECT SCOPE_IDENTITY()
END
ELSE
SELECT 0 AS DuplicateRecord

In 99.9% of the cases, this works well. However, in the event that the
insert attempts are literally "ticks" apart, the "SELECT TOP 1..."
command completes on both attempts before the first attempt completes.
So I end up with duplicate entries if the procedure is called multiple
times vey quickly. The system needs to prevent duplicate
EmployeeNumbers within the past 45 days so setting the EmployeeNumber
to UNIQUE would not work. I can check for older entries (45 days or
newer) very easily, but I do not know how to handle the times when the
procedure is called multiple times within milliseconds. Would a
TRANSACTION with a duplicate check after the INSERT with a ROLLBACK
work in this case? Any help is greatly appreciated!

-E

推荐答案

试试这个：


INSERT INTO tblRecords（employeenumber，arrivaltime）

SELECT @eloyloyumber，@ arrivaltime

WHERE NOT EXISTS

（SELECT *

FROM tblRecords

WHERE employeenumber = @eloyloyumber

AND arrivaltime> = DATEADD（DAY，-45，@ arrivaltime） ）;


发布时你的桌面设计有缺陷，因为它没有替代密钥。

IDENTITY永远不应该是桌子的唯一键。


-

David Portas

SQL Server MVP

-

Try this:

INSERT INTO tblRecords (employeenumber,arrivaltime)
SELECT @employeenumber, @arrivaltime
WHERE NOT EXISTS
(SELECT *
FROM tblRecords
WHERE employeenumber = @employeenumber
AND arrivaltime >= DATEADD(DAY,-45,@arrivaltime)) ;

As posted your table design is flawed because it has no alternate keys.
IDENTITY should never be the only key of a table.

--
David Portas
SQL Server MVP
--

更好的解决方案可能是使用触发器。


首先，添加最重要的UNIQUE约束：


ALTER TABLE tblRecords ADD CONSTRAINT ak_employees UNIQUE

（employeenumber，arrivaltime）


现在这里是一个触发器，将强制执行你的UPDATE业务规则为

以及INSERT：


CREATE TRIGGER trg_employee ON tblRecords FOR UPDATE，INSERT

AS

如果有EXISTS

（选择T1.employeenumber

FROM插入AS T1

加入tblrecords AS T2

ON T1.employeenumber = T2.employeenumber

AND T1.arrivaltime<> T2.arrivaltime

AND T2.arrivaltime< = DATEADD（DAY，45，T1.arrivaltime）

AND T2.arrivaltime> = DATEADD（DAY，-45， T1.arrivaltime））

BEGIN

ROLLBACK TRAN

RAISERROR（''员工人数在45天内重复'，16,1）< br $>
结束


GO


-

David Portas

SQL Server MVP

-

A better solution is probably to use a trigger.

First, add the all-important UNIQUE constraint:

ALTER TABLE tblRecords ADD CONSTRAINT ak_employees UNIQUE
(employeenumber,arrivaltime)

Now here''s a trigger which will enforce your business rule for UPDATEs as
well as INSERTs:

CREATE TRIGGER trg_employee ON tblRecords FOR UPDATE, INSERT
AS
IF EXISTS
(SELECT T1.employeenumber
FROM inserted AS T1
JOIN tblrecords AS T2
ON T1.employeenumber = T2.employeenumber
AND T1.arrivaltime <> T2.arrivaltime
AND T2.arrivaltime <= DATEADD(DAY,45,T1.arrivaltime)
AND T2.arrivaltime >= DATEADD(DAY,-45,T1.arrivaltime))
BEGIN
ROLLBACK TRAN
RAISERROR(''Employee number repeated within 45 days'',16,1)
END

GO

--
David Portas
SQL Server MVP
--

David Portas（RE *************** *************@acm.org）写道：

David Portas (RE****************************@acm.org) writes:

试试这个：

INSERT INTO tblRecords（employeenumber，arrivaltime）
SELECT @eloyloyumber，@ arrivaltime
不存在
（SELECT *
FROM tblRecords
WHERE employeenumber = @eloyloyumber
AND arrivaltime> = DATEADD（DAY， - 45，@ arrivaltime））;

Try this:

INSERT INTO tblRecords (employeenumber,arrivaltime)
SELECT @employeenumber, @arrivaltime
WHERE NOT EXISTS
(SELECT *
FROM tblRecords
WHERE employeenumber = @employeenumber
AND arrivaltime >= DATEADD(DAY,-45,@arrivaltime)) ;

这里需要可序列化的隔离级别。我认为SELECT语句中的UPDLOCK

是最好的。 （使用普通HOLDLOCK你可以

陷入僵局。）


至于触发器是否最好......你不必失眠结束

并发插入，但如果出现错误，你可以做更多工作（INSERT +

ROLLBACK）。是否需要错误消息我们不知道。再说一遍，

你可以同时拥有两者。强制执行业务规则的触发器，以及

应用程序代码，以提供良好的行为。


-

Erland Sommarskog ，SQL Server MVP， es****@sommarskog.se


联机丛书适用于SQL Server SP3
http：/ /www.microsoft.com/sql/techinf...2000/books.asp

这篇关于防止插入时出现重复行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！