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
