SQLTeam.com | Weblogs | Forums

SQL query to update before instert

I have SQL query with stored Procedure. Inserting records from Source toTarget table. I am unable to write query to update if records already there.
Please check the following query
USE [ZK_BIOTIME]
GO

/****** Object: StoredProcedure [dbo].[krsihna] Script Date: 12-Apr-21 7:46:17 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[krsihna]

AS

DECLARE @TodaysDate DATE
SET @TodaysDate = GETDATE()

BEGIN

UPDATE Dest
SET Dest.Device = Src.[sn_name]
	,Dest.[StaffName] = src.[name]
	,Dest.[Staff Id] = Src.[badgenumber]
	,Dest.[Entry Date] = Src.[date1]
	,Dest.[Actual Work time] = Src.[Total]
	,Dest.[Location Code] = Src.[location]
FROM nav17.dbo.[Kiddy Couture$Timesheet] Dest  
INNER JOIN ZK_BIOTIME.dbo.timeentry2 Src  
ON    cast(Dest.[Entry Date]  as nvarchar(30)) COLLATE Latin1_General_CI_AS  = cast(Src.[date1]       as nvarchar(30)) COLLATE Latin1_General_CI_AS 
 and  cast(Dest.[Staff Id]    as nvarchar(30)) COLLATE Latin1_General_CI_AS  = cast(Src.[badgenumber] as nvarchar(30)) COLLATE Latin1_General_CI_AS
WHERE cast(Src.[date1]        as nvarchar(30)) COLLATE Latin1_General_CI_AS  = cast(CONVERT(DATE, GETDATE()-1) as nvarchar(30)) COLLATE Latin1_General_CI_AS 
AND Src.sn_name COLLATE Latin1_General_CI_AS <> 'OAE6050066041900081'
AND ((Dest.Device COLLATE Latin1_General_CI_AS <> Src.[sn_name] COLLATE Latin1_General_CI_AS) OR 
	(Dest.[StaffName] COLLATE Latin1_General_CI_AS <> Src.[name] COLLATE Latin1_General_CI_AS) OR
	(Dest.[Staff Id] COLLATE Latin1_General_CI_AS <> Src.[badgenumber] COLLATE Latin1_General_CI_AS) OR
	(cast(Dest.[Entry Date]   as  nvarchar(30)) COLLATE Latin1_General_CI_AS <> cast(Src.[date1]  as nvarchar(30))  COLLATE Latin1_General_CI_AS ) OR
	(cast(Dest.[Starting Time] as nvarchar(30)) COLLATE Latin1_General_CI_AS <> cast(Src.[InTime] as nvarchar(30))  COLLATE Latin1_General_CI_AS) OR
	(cast(Dest.[Ending Time]   as nvarchar(30)) COLLATE Latin1_General_CI_AS <> cast(Src.[OutTime] as nvarchar(30))  COLLATE Latin1_General_CI_AS) OR
	(cast(Dest.[Actual Work time] as nvarchar(30)) COLLATE Latin1_General_CI_AS <> cast(Src.[Total] as nvarchar(30))  COLLATE Latin1_General_CI_AS) OR
	(Dest.[Location Code] COLLATE Latin1_General_CI_AS <> Src.[location] COLLATE Latin1_General_CI_AS));

INSERT INTO nav17.dbo.[Kiddy Couture$Timesheet] (
		Device
		,[StaffName]
		,[Staff Id]
		,[Entry Date]
		,[Starting Time]
		,[Ending Time]
		,[Actual Work time]
		,[Location Code]
		)
SELECT Src.sn_name, Src.name,Src.badgenumber,Src.date1,DATEADD(second, DATEDIFF(second, GETDATE(), GETUTCDATE()), Src.InTime),
DATEADD(second, DATEDIFF(second, GETDATE()-1, GETUTCDATE()), Src.OutTime),src.Total,'KDC'
FROM ZK_BIOTIME.dbo.timeentry2 AS Src
LEFT JOIN nav17.dbo.[Kiddy Couture$Timesheet] Dest
	ON cast(Dest.[Entry Date] as nvarchar(30)) COLLATE Latin1_General_CI_AS = cast(Src.date1 as nvarchar(30)) COLLATE Latin1_General_CI_AS 
	 and cast(Dest.[Staff Id] as nvarchar(30)) COLLATE Latin1_General_CI_AS = cast(Src.[badgenumber] as nvarchar(30)) COLLATE Latin1_General_CI_AS
WHERE Src.date1 = CONVERT(DATE, GETDATE()-1)
	AND Src.sn_name <> 'OAE6050066041900081'
	AND Dest.[Staff Id] IS NULL AND Dest.[Entry Date] IS NULL

END
GO

/-----------------
I want update these fields if records found
,[Ending Time]

Please help by changing the code above
Warm regards
Kris