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