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