Merging between two tables not working properly in SQl server

Question

Post reply

Merging between two tables not working properly in SQl server

techsavvy914

SSC Enthusiast

Points: 154
More actions
April 3, 2017 at 8:37 am

#323448

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

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

That is the behavior of MERGE. If you want a more granular update you should use separate INSERT and UPDATE statements.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

techsavvy914 - Monday, April 3, 2017 8:37 AM
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

You could use WHEN MATCHED AND (<<All your conditions go here>>) THEN

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Luis Cazares - Monday, April 3, 2017 9:42 AM
techsavvy914 - Monday, April 3, 2017 8:37 AM
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
You could use WHEN MATCHED AND (<<All your conditions go here>>) THEN

Missed that when looking at the documentation in BOL. Good catch.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

Lynn Pettis - Monday, April 3, 2017 9:57 AM
Luis Cazares - Monday, April 3, 2017 9:42 AM
You could use WHEN MATCHED AND (<<All your conditions go here>>) THEN
Missed that when looking at the documentation in BOL. Good catch.

I still don't like to code MERGE statements because I've been bitten by performance problems when using it.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Luis Cazares - Monday, April 3, 2017 10:13 AM
Lynn Pettis - Monday, April 3, 2017 9:57 AM
Luis Cazares - Monday, April 3, 2017 9:42 AM
You could use WHEN MATCHED AND (<<All your conditions go here>>) THEN
Missed that when looking at the documentation in BOL. Good catch.
I still don't like to code MERGE statements because I've been bitten by performance problems when using it.

I have only used it when it really made sense, which is only a few times.

techsavvy914 SSC Enthusiast Points: 154 More actions · Answer 6

Lynn Pettis - Monday, April 3, 2017 9:26 AM
That is the behavior of MERGE. If you want a more granular update you should use separate INSERT and UPDATE statements.

Hi Lynnn,

Thank you for the reply.

Could you please let me know the alternatives as you mentioned above " separate INSERT and UPDATE statements".

Could you please provide the sample query.

techsavvy914 SSC Enthusiast Points: 154 More actions · Answer 7

Luis Cazares - Monday, April 3, 2017 10:13 AM
Lynn Pettis - Monday, April 3, 2017 9:57 AM
Luis Cazares - Monday, April 3, 2017 9:42 AM
Luis Cazares - Monday, April 3, 2017 10:13 AM
Lynn Pettis - Monday, April 3, 2017 9:57 AM
Luis Cazares - Monday, April 3, 2017 9:42 AM
You could use WHEN MATCHED AND (<<All your conditions go here>>) THEN
Missed that when looking at the documentation in BOL. Good catch.
I still don't like to code MERGE statements because I've been bitten by performance problems when using it.
You could use WHEN MATCHED AND (<<All your conditions go here>>) THEN
Missed that when looking at the documentation in BOL. Good catch.
I still don't like to code MERGE statements because I've been bitten by performance problems when using it.

Hi Luis,

Thank you for the reply.

Could you please let me know the alternatives

Could you please provide the sample query.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 8

This is a copy-paste from your code and might have syntax issues or might not work as intended, but it's meant to be a guide for you.
UPDATE TT 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] FROM [dbo].[tblEmployees_Test] TT JOIN [dbo].[Efiling_Test] ST ON (TT.fldEmployeeID = ST.Staff_No)

INSERT INTO [dbo].[tblEmployees_Test] (fldEmployeeID, fldEmpName, fldEmpLastName, PersonType, Grade, SBU,fldStartingDate,fldLeaversDate,fldEmailAddress, fldOfficeLocation,fldLineManagerName,fldLineManagerStaffNumber,fldBU,fldDepartment,fldOperation,fldEmployeeCategorisation,fldRegion) SELECT 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 FROM [dbo].[Efiling_Test] WHERE NOT EXISTS( SELECT * FROM [dbo].[tblEmployees_Test] WHERE TT.fldEmployeeID = ST.Staff_No)

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2