question

SQLKrishna avatar image
SQLKrishna asked

How to insert or update records in another table

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

updateinsert-exec-sql
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

anthony.green avatar image
anthony.green answered

Sound like the need to add this to the update list

,Dest.[Ending Time] = Src.[OutTime]


10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

SQLKrishna avatar image
SQLKrishna answered

Laster I have added . but end time inserted with next date

Thanks for the reply

I am new to SQL queries

I have changed the code but it is not working.

for update it is taking yesterday time in, as today time out. if records find for yesterday (today-1) , it should update

USE [ZK_BIOTIME]
GO
/****** Object: StoredProcedure [dbo].[krsihna] Script Date: 21-Apr-21 10:31:58 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER 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.[Starting Time] = DATEADD(second, DATEDIFF(second, GETDATE()-1, GETUTCDATE())-1, Src.InTime)
,Dest.[Ending Time] = DATEADD(second, DATEDIFF(second, GETDATE()-1, GETUTCDATE())-1, Src.OutTime)
,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

SOURCE QUOERY RESTULT WITH QUERY

select BADGENUMBER, date1,Intime,Outtime,ISNULL(Total,0) as Total,sn_name
from timeentry2 where Date1='2021-04-20'


BADGENUMBERdate1IntimeOuttime100920/04/2155:43.001:26.0101020/04/2122:10.000:28.0101520/04/2146:36.001:46.0102520/04/2152:26.052:27.0102620/04/2158:09.052:46.0102720/04/2128:24.052:17.0102820/04/2108:28.049:44.0102920/04/2128:00.052:48.0103020/04/2112:02.000:38.0103120/04/2141:25.041:25.0200920/04/2134:25.001:20.0201220/04/2149:59.058:13.0201620/04/2146:41.000:29.0201720/04/2152:57.000:51.0202020/04/2100:06.002:45.0202120/04/2146:16.000:04.0202320/04/2149:50.057:18.0202420/04/2154:45.003:45.0202520/04/2121:19.021:19.0202620/04/2145:33.001:16.0

result


Entry DateStaff IdStarting TimeEnding TimeActual Work timeNameDeviceLocation CodeStaffName20/04/21103120/04/21 12:4121/04/21 12:41 Dominga Rosadiño BoadoOAE6050086042300015KDCDominga20/04/21103020/04/21 12:1221/04/21 23:00648 millisecondsGherwin Sebaria MojadoOAE7040037042100030KDCGherwin20/04/21102920/04/21 11:2821/04/21 22:52684 millisecondsJesus Christopher Cortuna Abella OAE6050086042300030KDCJesus20/04/21102820/04/21 12:0821/04/21 21:49581 millisecondsJomarOAE7040037042100041KDCJOMAR20/04/21102720/04/21 11:2821/04/21 22:52684 millisecondsMagdalene Cariaga AntonioOAE6050086042300030KDCMAGDALENE20/04/21102620/04/21 12:5821/04/21 22:52594 millisecondsMary Ann3.37E+12KDCMARY ANN20/04/21102520/04/21 9:5221/04/21 22:52780 millisecondsJerry Gaceta Peralta3.37E+12KDCJERRY20/04/21101520/04/21 11:4621/04/21 23:01675 millisecondsRubelynOAE7040037042100030KDCRUBELYN20/04/21101020/04/21 11:2221/04/21 22:00638 millisecondsEricOAE6050086042300015KDCERIC20/04/21100920/04/21 12:5521/04/21 23:01606 millisecondsCharlieOAE6050086042300015KDCCHARLIE
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

SQLKrishna avatar image
SQLKrishna answered

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.