Updating a SQL Server Table with data that does not exist

Question

I have the below script that served it's time and I need some modifications done. The script runs 2 to 3 times daily to import data from the table. Some data may be duplicated and what I want is that the modified script will only import and write to the table if the data does not exist.

Someone mentioned that I can try to merge the data or use Where Not Exist.

Unfortunately I'm not sure how I will implement either and hence I've turned to you all for help. Any help will be greatly appreciated.

BEGIN TRY
BEGIN TRANSACTION 
Declare @Date As DATE

IF OBJECT_ID('Tempdb..#1','U') IS NOT NULL
DROP TABLE #1

SELECT [EMP_ID],[NAME],[DEPT],[DATE],[Start Time],[LOGIN],[LATE/AWOL],[Team Manager],[Senior Manager],[SITE]
INTO #1
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
                 'Excel 12.0;Database=\\VF_RSOSDCNASUDA\xxx\SQL\ESO\ALLAttendance.xlsm;HDR=YES',
                 'SELECT * FROM [ForEmail$]');

SELECT TOP 1 @Date=[DATE] FROM #1

DELETE [dbo].[ALLAttendance$] WHERE CONVERT(char(10),[Date],102)=@Date;

INSERT INTO [dbo].[ALLAttendance$]
SELECT [EMP_ID],[NAME],[DEPT],[DATE],[Start Time],[LOGIN],[LATE/AWOL],[Team Manager],[Senior Manager],[SITE]
FROM #1
DROP TABLE #1
COMMIT
END TRY
BEGIN CATCH

IF @@TRANCOUNT > 0
    ROLLBACK
END CATCH

*****UPDATE***** I came up with the below and it appears to work but not sure if it's the correct way of doing this

with CTE
AS
(
SELECT [EMP_ID]
   ,[NAME]
   ,[DEPT]
   ,[DATE]
   ,[Start Time]
   ,[LOGIN]
   ,[LATE/AWOL]
   ,[Team Manager]
   ,[Senior Manager]
   ,[SITE]
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
                 'Excel 12.0;Database=\\VF_RSOSDCNASUDA\xxx\SQL\ESO\ALLAttendance.xlsm;HDR=YES',
                 'SELECT * FROM [ForEmail$]')
)
merge [dbo].[ALLAttendance$] t
using CTE s
on s.[EMP_ID] = t.[EMP_ID]
and s.[NAME] = t.[NAME]
and s.[DEPT] = t.[DEPT]
and s.[DATE] = t.[DATE]
and s.[Start Time] = t.[Start Time]
and s.[LOGIN] = t.[LOGIN]
and s.[LATE/AWOL] = t.[LATE/AWOL]
and s.[Team Manager] = t.[Team Manager]
and s.[Senior Manager] = t.[Senior Manager]
and s.[SITE] = t.[SITE]
when not matched by target
then insert ([EMP_ID],[NAME],[DEPT],[DATE],[Start Time],[LOGIN],[LATE/AWOL],[Team Manager],[Senior Manager],[SITE]) 
values (s.[EMP_ID],s.[NAME],s.[DEPT],s.[DATE],s.[Start Time],s.[LOGIN],s.[LATE/AWOL],s.[Team Manager],s.[Senior Manager],s.[SITE]);

Answer 1

My final solution to the issue is below.

BEGIN TRY
BEGIN TRANSACTION 
;with CTE
AS
(
SELECT [EMP_ID]
   ,[NAME]
   ,[DEPT]
   ,[DATE]
   ,[Start Time]
   ,[LOGIN]
   ,[LATE/AWOL]
   ,[Team Manager]
   ,[Senior Manager]
   ,[SITE]
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
                 'Excel 12.0;Database=\\VF_RSOSDCNASUDA\xxx\National_Command\National Command Centre\SQL\ESO\ALLAttendance.xlsm;HDR=YES',
                 'SELECT * FROM [ForEmail$]')
)
merge ESO.[dbo].[ALLAttendance$] t
using CTE s
on s.[EMP_ID] = t.[EMP_ID]
and s.[NAME] = t.[NAME]
and s.[DEPT] = t.[DEPT]
and s.[DATE] = t.[DATE]
and s.[Start Time] = t.[Start Time]
and s.[LOGIN] = t.[LOGIN]
and s.[LATE/AWOL] = t.[LATE/AWOL]
and s.[Team Manager] = t.[Team Manager]
and s.[Senior Manager] = t.[Senior Manager]
and s.[SITE] = t.[SITE]
when not matched by target
then insert ([EMP_ID],[NAME],[DEPT],[DATE],[Start Time],[LOGIN],[LATE/AWOL],[Team Manager],[Senior Manager],[SITE]) 
values (s.[EMP_ID],s.[NAME],s.[DEPT],s.[DATE],s.[Start Time],s.[LOGIN],s.[LATE/AWOL],s.[Team Manager],s.[Senior Manager],s.[SITE]);
COMMIT
END TRY
BEGIN CATCH

IF @@TRANCOUNT > 0
    ROLLBACK
END CATCH