Restrict to insert duplicate record

Question

I have data below

Create table #EmpMaster (EnrollNumber int, empName varchar(50))  
                   Create table #MonthDate (Day varchar(50),Date date,Holiday varchar(50))  
               Create table #AttendenceLOG (EnrollNumber int,A_Date datetime,H_status int)  

  
 Create table #tbl_Emp_Register  ( EnrollNumbeR int,Date date,Day varchar(4),TimeIN time(7),TimeOut time(7),Hour int,Duty int, OT int)  
  
                  Insert into #EmpMaster values (10001,'ALi')  
           Insert into #EmpMaster values (10002,'Hussain')  
                     Insert into #MonthDate values ('Wednesday','2022-11-01',null)   
                     Insert into #MonthDate values ('Thursday','2022-11-02',null)   
                     Insert into #MonthDate values ('Friday','2022-11-03',null)   
                     Insert into #MonthDate values ('saturday','2022-11-04',null)   
                     Insert into #MonthDate values ('Sunday','2022-11-05',null)   
  
                  
                  
                          
                Insert into #AttendenceLOG values (10001,'2022-11-01 08:04:00',null)  
                Insert into #AttendenceLOG values (10001,'2022-11-01 17:45:00',null)  
                Insert into #AttendenceLOG values (10001,'2022-11-02 08:04:00',null)  
                Insert into #AttendenceLOG values (10001,'2022-11-02 17:45:00',null)  
                 
                 Insert into #AttendenceLOG values (10001,'2022-11-03 11:45:00',null)  
                Insert into #AttendenceLOG values (10001,'2022-11-03 18:45:00',null)  

after executing below query

if NOt EXISTS  
 (Select    EnrollNumbeR,Date from #tbl_Emp_Register  
 where EnrollNumbeR=EnrollNumbeR and Date=Date  
   
 )  
 begin  
with   
cte as (  
  
 select a.EnrollNumber,b.Date,b.Day,b.Holiday,c.A_Time A_Date  from #EmpMaster a   
     
 cross join #MonthDate b  
 left join #AttendenceLOG c   
 on a.EnrollNumber=c.EnrollNumber   
 and cast(c.A_Date as date)=b.Date  
  
 where a.emp_Dol is null  
 )  
   
  
 ,cte1 AS (  
 SELECT  EnrollNumbeR,Date,Day,Holiday  
 ,MIN(A_Date) [IN(A_Date)],MAX(A_Date) [OUT(A_Date)]  
 FROM CTE   
 GROUP BY EnrollNumbeR,Date,Day,Holiday)  
   
 ,Cte2 as (  
 SELECT EnrollNumbeR,Date,LEFT(Day,3)Day,Holiday  
 ,CASE WHEN [IN(A_Date)] >=DATEADD(HOUR,12,CAST(([IN(A_Date)] ) as Time(7))) THEN NULL ELSE [IN(A_Date)] END TimeIN  
 ,CASE WHEN [OUT(A_Date)] >=DATEADD(HOUR,12,CAST(([OUT(A_Date)] ) AS time(7))) THEN [OUT(A_Date)] ELSE NULL END TimeOut  
 FROM cte1  
 )  
 , cte3 as (  
 Select EnrollNumbeR,Date,LEFT(Day,3)Day,Holiday,TimeIN,TimeOut,  
   
  CASE WHEN TimeIN IS NOT NULL AND TimeOut IS NOT NULL AND DATEDIFF(HOUR,TimeIN,TimeOut)>=8   
 THEN CAST(CAST(DATEDIFF(MINUTE,TimeIN,TimeOut) AS FLOAT)/60 AS decimal(10,2)) ELSE 0 END [HOUR]  
   
     from  cte2  
  ),cte_F as (  
  Select EnrollNumbeR,Date,LEFT(Day,3)Day,Holiday,TimeIN,TimeOut,Round(Hour,0)Hour,  
  case when Hour>=8 then 1 when Hour=4 then 0.5 else 0 End Duty,  
  case when Hour>9 then  Round(Hour-9,0) end OT  
 from cte3)  
  
 insert  tbl_Emp_register  ( EnrollNumbeR,Date,Day,Holiday,TimeIN,TimeOut,Hour,Duty, OT)  
  
   Select EnrollNumbeR,Date,Day,Holiday,TimeIN,TimeOut,Hour,  
   Duty,  
   OT  
 from cte_F  
  
 where Date between '2022-11-01' and '2022-11-16' --and date<@Todaydate  
 end  
                          

I am inserting record into table #tbl_Emp_Register,but i want those record which is not exists in table,before inserting it will check enrollnumber and Date ,if exists then record will not insert other wise will be inserted

i have data in table below already

Answer 1

Hi @Analyst_SQL
Maybe like this:

INSERT INTO #tbl_Emp_register(EnrollNumbeR,Date,Day,TimeIN,TimeOut,Hour,Duty, OT)     
SELECT EnrollNumbeR,Date,Day,TimeIN,TimeOut,Hour,Duty,OT  
FROM cte_F  
WHERE Date between '2022-11-01' and '2022-11-16' --and date<@Todaydate  
  AND NOT EXISTS(SELECT EnrollNumbeR FROM #tbl_Emp_register  
                 WHERE #tbl_Emp_register.EnrollNumbeR = cte_F.EnrollNumbeR   
				   AND #tbl_Emp_register.Date = cte_F.Date)  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hello,

not expert but add a constraint on your table for example like this

ALTER TABLE dbo.YourTableE ADD CONSTRAINT constraintName UNIQUE(enrollNumber, date);

try and tell us

Answer 3

Make simple a select query statement with a where clause :

SELECT field1, field2 from yourTable where enrollNumber = @enrollNumber and date = @apl

and check if result contains one item