CODEWITHSUNDEEP
t-sqlsql-server-2012
Thomas Sharp
Thomas Sharp

Reputation: 121

Merge Statement overwriting entire row if Matched

I am trying to update records in a Sql Server 2012 table by using a MERGE statement. I have my target table and my source staging table. When the ID field matches I want to replace the entire row with the matching row from the source, and if not, add the row to the table as a new row. I don't know what to write after update set (?) to achieve this. here is my code: 

    USE DataCompany
MERGE INTO MasterAddress ma
   USING MasterAddressStaging ms
      ON MasterAddress.ID = MasterAddressStaging.ID
WHEN MATCHED THEN
   UPDATE SET (?)
WHEN NOT MATCHED THEN 
INSERT (ID
        ,CompanyName
        ,CompanyNumber
        ,RegAddress_CareOf
        ,RegAddress_POBox
        ,RegAddress_AddressLine1 
        ,RegAddress_AddressLine2 
        ,RegAddress_PostTown 
        ,RegAddress_County 
        ,RegAddress_Country 
        ,RegAddress_PostCode)
VALUES (ms.ID
        ,ms.CompanyName
        ,ms.CompanyNumber
        ,ms.RegAddress_CareOf
        ,ms.RegAddress_POBox
        ,ms.RegAddress_AddressLine1 
        ,ms.RegAddress_AddressLine2 
        ,ms.RegAddress_PostTown 
        ,ms.RegAddress_County 
        ,ms.RegAddress_Country 
        ,ms.RegAddress_PostCode);

thanks for your help

Upvotes: 1

Views: 1570

Answers (1)

mcr
mcr

Reputation: 782

  USE DataCompany
  MERGE INTO MasterAddress ma
     USING MasterAddressStaging ms
        ON ma.ID = ms.ID
  WHEN MATCHED THEN
     UPDATE SET ma.CompanyName = ms.CompanyName,
                ma.CompanyNumber = ms.CompanyNumber,
                ma.RegAddress_CareOf =ms.RegAddress_CareOf,
                ma.RegAddress_POBox =ms.RegAddress_POBox,
                ma.RegAddress_AddressLine1 = ms.RegAddress_AddressLine1,
                ma.RegAddress_AddressLine2 = ms.RegAddress_AddressLine2,
                ma.RegAddress_PostTown = ms.RegAddress_PostTown,
                ma.RegAddress_County = ms.RegAddress_County,
                ma.RegAddress_Country = ms.RegAddress_Country,
                ma.RegAddress_PostCode = ms.RegAddress_PostCode
  WHEN NOT MATCHED THEN 
  INSERT (ID
          ,CompanyName
          ,CompanyNumber
          ,RegAddress_CareOf
          ,RegAddress_POBox
          ,RegAddress_AddressLine1 
          ,RegAddress_AddressLine2 
          ,RegAddress_PostTown 
          ,RegAddress_County 
          ,RegAddress_Country 
          ,RegAddress_PostCode)
  VALUES (ms.ID
          ,ms.CompanyName
          ,ms.CompanyNumber
          ,ms.RegAddress_CareOf
          ,ms.RegAddress_POBox
          ,ms.RegAddress_AddressLine1 
          ,ms.RegAddress_AddressLine2 
          ,ms.RegAddress_PostTown 
          ,ms.RegAddress_County 
          ,ms.RegAddress_Country 
          ,ms.RegAddress_PostCode);

See examples here

Upvotes: 2

Related Questions