On Merge Command "Attempting to set a non-NULL-able column's value to NULL." getting erroe
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
First of all, i would use "when not matched by target" (even IF it is optional to leave it, take it as à best practice to always specify optional statements). I am not sure that target is optional since you have the when not matched by source statement in merge, but i can't verify that right now. Second, have you specified all the columns in the insert statement? IF not, null values Will be inserted.
Hi Oleg, On re-checking the Merge SQL Statement. i found the issue. In Merge Update statement updating the Timestamp,Empid whic are use in Cluster index definations. On excluding these columns from update. Merge command work fine. Samir
I had this same problem on 2008 SP3. Seems there is a SQL Server Bug here, but there is a way to fix. For my problem the statistics were producing a bad query plan and I think if you see "Table Spool" during a Merge statement you may encounter this error. To fix I rebuilt the indexes on the whole table: ALTER INDEX ALL ON REBUILD To avoid this from occurring again, I'll be looking at my current indexes on the table, some of them are redundant and can be removed/refactored