我为插入记录写了一个查询,但有时它会插入重复的记录 我的查询是这样的 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)更多推荐
如何避免重复插入数据库?
发布评论