Merging between two tables not working properly

Transact-SQL
1

I am using the Merge functionality of MS Sql Server 2008 to update or insert records if there is difference in data/rows between two tables.

In the below merge query [tblEmployees_Test] is the destination table & [Efiling_Test] is the source table.

All the rows of table [tblEmployees_Test] gets updated, I want only rows with difference in the column values to get updated

Please find below the MERGE query

MERGE [dbo].[tblEmployees_Test] TT
USING [dbo].[Efiling_Test] ST
ON (TT.fldEmployeeID = ST.Staff_No
)
-- This inserts new records in the dimension table
WHEN NOT MATCHED THEN
INSERT (fldEmployeeID, fldEmpName, fldEmpLastName, PersonType, Grade, SBU,fldStartingDate,fldLeaversDate,fldEmailAddress,
fldOfficeLocation,fldLineManagerName,fldLineManagerStaffNumber,fldBU,fldDepartment,fldOperation,fldEmployeeCategorisation,fldRegion)
VALUES (ST.Staff_No, ST.First_Name, ST.Last_Name, ST.[Person_Type], ST.Grade, ST.[Org_L4_Name],ST.[Start_Date],ST.[Leaving_Date],ST.[Email],
ST.[Current_Office],ST.[Line_Manager_Name],ST.[Line_Manager_Staff_No],ST.[Org_L7_Name],ST.[Org_L8_Name],ST.[Org_6_Code],ST.[Staff_Category],ST.Region)
WHEN MATCHED THEN
UPDATE SET

		  TT.fldEmployeeID = ST.Staff_No , TT.fldEmpName = ST.First_Name 
		  , fldEmpLastName = Last_Name , PersonType = [Person_Type] , TT.Grade = ST.Grade 
		  , SBU =[Org_L4_Name] , fldStartingDate = [Start_Date] , fldLeaversDate =[Leaving_Date]
		  , fldEmailAddress = [Email] , fldOfficeLocation = [Current_Office] 
		  , fldLineManagerName = [Line_Manager_Name] , fldLineManagerStaffNumber = [Line_Manager_Staff_No]
		  , fldBU = [Org_L7_Name] , fldDepartment = [Org_L8_Name] 
		  , fldOperation = [Org_6_Code] 
		  , fldEmployeeCategorisation = [Staff_Category] , fldRegion = [Region]
                OUTPUT $Action Action_Taken, ST.Staff_No, ST.First_Name, ST.Last_Name, ST.[Person_Type], ST.Grade, ST.[Org_L4_Name],ST.[Start_Date],ST.[Leaving_Date],ST.[Email],

ST.[Current_Office],ST.[Line_Manager_Name],ST.[Line_Manager_Staff_No],ST.[Org_L7_Name],ST.[Org_L8_Name],ST.[Org_6_Code],ST.[Staff_Category],ST.Region
;

Please provide suggestions to update specific rows.

Thanks in Advance

2

You need to include the condition on when to update:

WHEN MATCHED
 AND (
     tt.fldEmployeeID <> st.Staff_No
  OR tt.fldEmpName <> st.First_Name
  OR tt.fldEmpLastName <> st.Last_Name
 ...
  OR tt.fldRegion <> st.Region
     )
THEN UPDATE
 SET ...
OUTPUT $Action Action_Taken, ...
1 Like
3

Hi Jeffw8713,

I tried your above logic.It is not updating any rows.

Any other alternatives. ?

4

When you select using the same join criteria, do you get a result?

5

Are you sure there are rows that qualify to be updated? I think you will find that no rows were selected because none of the rows meet the requirement.

Note: if the source or destination column contains a NULL the condition will not be matched and the row will not be selected. If that is the case then your condition has to account for NULL values.