如何避免重复插入数据库？ [英] How to avoid duplicate insert into database?

问题描述

我为插入记录写了一个查询，但有时它会插入重复的记录

我的查询是这样的





DECLARE @cnt AS INT

SET @ cnt =（SELECT COUNT（*）FROM

tbltest

WHERE

Empcode = @Uid AND

SID = @Sid AND

PID = @Pid AND

EmpID = @ EmpId AND

RMID = @RMId AND

EndTime IS NULL）



IF @ cnt = 0

BEGIN

插入到tbltest（EmpCode，sid，Pid，Empid，RmId，Param1，Starttime，Transtype）值（

@Uid，

@Sid，

@Pid，

@EmpId，

@RMId，

@ processname_param1，@ CURRDATE ，'输入'）

结束



i使用代码避免重复，但有时会插入重复记录。

解决方案

在此表中添加一个主键。

这个主键是这些列的组合，它们共同定义一个唯一的字段。



偶你不想定义主键，你可以定义一个唯一约束 - 创建唯一约束 [ ^ ]。

尝试在您的选择查询中删除 AND EndTime为空 过滤并再次检查

  SET  @cnt =（ SELECT  COUNT（*） FROM  
 tbltest 
  WHERE  
 Empcode =  @ Uid   AND  
 SID =  @ Sid   AND  
 PID =  @ Pid   AND  
 EmpID = @ EmpId  AND  
 RMID =  @ RMId ）

I write a query for insert records but some times it insert duplicate records
my query like this


DECLARE @cnt AS INT
SET @cnt=(SELECT COUNT(*) FROM
tbltest
WHERE
Empcode= @Uid AND
SID= @Sid AND
PID= @Pid AND
EmpID=@EmpId AND
RMID= @RMId AND
EndTime IS NULL)

IF @cnt=0
BEGIN
insert into tbltest (EmpCode,sid,Pid,Empid,RmId,Param1,Starttime,Transtype) values(
@Uid,
@Sid,
@Pid,
@EmpId,
@RMId,
@processname_param1,@CURRDATE,'Input')
END

i used code for avoiding duplication but some times it insert duplicate records.

解决方案

Add a primary key to this table.
This primary key would be a combination of those columns that together define a unique field.

Even inf you don't want to define a primary key, you can define a unique constraint - Create Unique Constraints[^].

Try removing AND EndTime is NULL filter in your select query and check again

SET @cnt=(SELECT COUNT(*) FROM 
tbltest 
WHERE 
Empcode= @Uid AND 
SID= @Sid AND 
PID= @Pid AND 
EmpID=@EmpId AND 
RMID= @RMId) 

这篇关于如何避免重复插入数据库？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！