从一张表到另一张表插入没有重复记录

Question

我想从表T1复制数据到表T2到以下条件： 1）数据不应该重复。 2）和T1列签入和T2签入的时间差必须超过5秒。

我现有的程序这

ALTER PROCEDURE [dbo].[usp_InsertUserAttendanceLog] @userId varchar(400),@CheckInCheckOutDate datetime,@WorkDate datetime,@InOutMode int,@VerifyMode int, @InsertDate datetime 
AS 
BEGIN 
if 
not exists (select 1 from tblAttendance where [email protected] and [email protected] and [email protected] and [email protected]) 
begin 
if 
not exists (select 1 from tblAttendance where [email protected] and convert(date,CheckInCheckOutDate)=convert(date,@CheckInCheckOutDate) and DATEDIFF(second,CheckInCheckOutDate,@CheckInCheckOutDate) <= 3) 
begin 
INSERT INTO tblAttendance(EmpCode,CheckInCheckOutDate,WorkDate,InoutMode,VerifyMode,InsertDate) 
VALUES(@userId,@CheckInCheckOutDate,@WorkDate,@InOutMode,@VerifyMode,@InsertDate) 
end 
end 
end 

和我tryed这样的代码，但不起作用。葛亭Dupicate

INSERT INTO tblAttendance (EmpCode,CheckInCheckOutDate,WorkDate,InOutMode,VerifyMode,InsertDate,[Status]) 
SELECT T1.EmpCode,T1.CheckInCheckOutDate,T1.WorkDate,T1.InOutMode,T1.VerifyMode,T1.InsertDate,T1.[Status] 
    FROM testAttendanceBulk T1 
WHERE NOT EXISTS(select 1 from tblAttendance T2 where T2.CheckInCheckOutDate=T1.CheckInCheckOutDate 
and T2.WorkDate=T1.WorkDate and T2.InOutMode=T1.InOutMode 
and T2.VerifyMode=T1.VerifyMode and T2.empCode=T1.EmpCode 
and convert(date,T2.CheckInCheckOutDate)=convert(date,T1.CheckInCheckOutDate) 
and DATEDIFF(second,T2.CheckInCheckOutDate,T1.CheckInCheckOutDate) <= 5) 

it's my table structure

Answer 1

很确定你可以使用类似这样的东西。请注意我对DATEFIFF谓词的评论，它可能是您面临的问题的一部分。

INSERT INTO tblAttendance 
(
    EmpCode 
    , CheckInCheckOutDate 
    , WorkDate 
    , InOutMode 
    , VerifyMode 
    , InsertDate 
    , [Status] 
) 
SELECT ab.EmpCode 
    , ab.CheckInCheckOutDate 
    , ab.WorkDate 
    , ab.InOutMode 
    , ab.VerifyMode 
    , ab.InsertDate 
    , ab.[Status] 
FROM testAttendanceBulk ab 
LEFT JOIN tblAttendance a ON a.CheckInCheckOutDate = ab.CheckInCheckOutDate 
    AND a.WorkDate = ab.WorkDate 
    AND a.InOutMode = ab.InOutMode 
    AND isnull(a.VerifyMode, 0) = isnull(ab.VerifyMode, 0) 
    AND a.empCode = ab.EmpCode 
    AND CONVERT(DATE, a.CheckInCheckOutDate) = CONVERT(DATE, ab.CheckInCheckOutDate) 
    AND DATEDIFF(SECOND, a.CheckInCheckOutDate, ab.CheckInCheckOutDate) <= 5 --do you really want 5 here? Your original query had 3. 
WHERE a.empCode IS NULL 

Answer 2

在你加入，你正在检查是否CheckinCheckoutDate是相等的，如果有> 5秒的差异。尝试摆脱t1.CheckinCheckoutDate = T2.CheckinCheckoutDate;

INSERT 
     INTO tblAttendance 
      (EmpCode, 
      CheckInCheckOutDate, 
      WorkDate, 
      InOutMode, 
      VerifyMode, 
      InsertDate, 
      [Status]) 
    SELECT 
    DISTINCT T1.EmpCode, 
      T1.CheckInCheckOutDate, 
      T1.WorkDate, 
      T1.InOutMode, 
      T1.VerifyMode, 
      T1.InsertDate, 
      T1.[Status] 
     FROM testAttendanceBulk T1 
    WHERE NOT EXISTS(SELECT T1.EmpCode, 
          T1.CheckInCheckOutDate, 
          T1.WorkDate, 
          T1.InOutMode, 
          T1.VerifyMode, 
          T1.InsertDate, 
          T1.[Status] 
         FROM tblAttendance T2 
         WHERE 1=1 --T2.CheckInCheckOutDate=T1.CheckInCheckOutDate 
         AND T2.WorkDate=T1.WorkDate 
         AND T2.InOutMode=T1.InOutMode 
         AND T2.VerifyMode=T1.VerifyMode 
         AND T2.empCode=T1.EmpCode 
         AND convert(date,T2.CheckInCheckOutDate)=convert(date,T1.CheckInCheckOutDate) 
         AND DATEDIFF(second,T2.CheckInCheckOutDate,T1.CheckInCheckOutDate) <= 5)