如何避免重复插入数据库? [英] 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屋!