question

Hi SQLTeam, I am using the Merge command to update/insert the data into table. The select statement is not returning any NULL values. Sample Query for Merge which fails: MERGE tblAgentLoginStat AS Target USING ( Select Timestamp,EmpGUID,EmpID,ServerID,Time,EventType,Duration,PositionID from (SELECT ISNULL(e.LoginTimestampLocal, e.LogoutTimestampLocal) AS Timestamp , a.EmpGUID AS EmpGUID, Emp.EmpID AS EmpID, e.ServerID AS ServerID , CAST(CONVERT(Time, ISNULL(e.LoginTimestampLocal, e.LogoutTimestampLocal), 108) AS nvarchar(8)) AS Time , CASE WHEN e.LoginTimestampLocal IS NULL THEN 'LO' ELSE 'LI' End AS EventType , CASE WHEN e.LoginTimestampLocal IS NULL THEN DATEDIFF(Second, x.LoginTimestampLocal, x.LogoutTimestampLocal) ELSE 0 END AS Duration , e.extn AS PositionID FROM tblAvayaCMShagLog e LEFT JOIN tblAvayaCMSAgent a ON e.LogId = a.AvayaLoginID AND e.EmpGuid=a.EmpGuid LEFT JOIN tblEmp Emp ON Emp.EmpGUID = e.EmpGUID LEFT JOIN tblAvayaCMShagLog x ON x.EmpGUID = e.EmpGUID AND x.ServerID = e.ServerID AND x.Row_Date = e.Row_Date AND x.LogoutTimestampLocal = e.LogoutTimestampLocal AND x.LoginTimestampLocal IS NOT NULL WHERE (e.EmpGUID IS NOT NULL) AND (e.LogoutTimestampLocal IS NOT NULL) AND (e.Row_date>= '20100801 00:00:00') AND (e.Row_date< '20100802 00:00:00') ) S ) AS Source ON (Target.Timestamp = Source.Timestamp OR (Target.Timestamp IS NULL AND Source.Timestamp IS NULL) ) AND (Target.EmpGUID = Source.EmpGUID OR (Target.EmpGUID IS NULL AND Source.EmpGUID IS NULL) ) AND (Target.ServerID = Source.ServerID OR (Target.ServerID IS NULL AND Source.ServerID IS NULL) ) AND (Target.EventType = Source.EventType OR (Target.EventType IS NULL AND Source.EventType IS NULL) ) WHEN MATCHED THEN UPDATE SET Target.Timestamp = Source.Timestamp, Target.EmpGUID = Source.EmpGUID, Target.EmpID = Source.EmpID, Target.ServerID = Source.ServerID, Target.Time = Source.Time, Target.EventType = Source.EventType, Target.Duration = Source.Duration, Target.PositionID = Source.PositionID, Target.SystemID = 25, Target.DateChanged = GETDATE() WHEN NOT MATCHED THEN INSERT (AgentLoginStatGuid, Timestamp, EmpGUID, EmpID, ServerID, Time, EventType, Duration, PositionID, SystemID, DateChanged) VALUES (NEWID(), Timestamp, EmpGUID, EmpID, ServerID, Time, EventType, Duration, PositionID, 25, GETDATE()) ; Thanks in advance Samir