MERGE INTO

• NikosV

  SSCrazy

  Points: 2111

  More actions

  September 20, 2018 at 1:01 am

  #363716

  Hi guys,

  I'm a bit confused as to what I'm doing wrong here.

  I've got a table type into which Excel data is being imported from an application and from that table type, the data goes into a normal table.

  What I need is this:

  1. When records of the same month exist in Target table, replace the Premium and the Cases values in the target table with those from the Source Table.

  2. If the months differ, insert the values as per normal.

  3. If records in the source table exists that aren't in the target table and the months are the same, insert those into target table.

  Here is my current code which currently replaces all values regardless of the month given.

  
ALTER PROCEDURE [dbo].[BR_INSERT_DATA_FROM_BANK_WORKSHEET_TESTER]

        @excelTableType ExcelTableType READONLY,
     @month INT,
     @year INT

  AS
  BEGIN

  MERGE INTO BR_LIMIT_INSURANCES_TEST_TWO AS TargTable USING
    (SELECT Branch,
      CONVERT(MONEY,REPLACE(Premium,',','.' )),
      Cases,
      @month,
      @year
     FROM @excelTableType
    )AS SourceTable (Branch, Premium, Cases, ReportMonth, ReportYear)
    
     ON TargTable.Branch = SourceTable.Branch
     AND TargTable.ReportMonth = SourceTable.ReportMonth
     AND TargTable.ReportYear = SourceTable.ReportYear

  WHEN MATCHED
  THEN UPDATE
    SET TargTable.Premium = SourceTable.Premium,
     TargTable.Cases = SourceTable.Cases

  WHEN NOT MATCHED BY TARGET
  THEN
   INSERT VALUES (SourceTable.Branch,
         SourceTable.Premium,
         SourceTable.Cases,
         SourceTable.ReportMonth,
         SourceTable.ReportYear)
  WHEN NOT MATCHED BY SOURCE
  THEN
   DELETE;
  END

  Happy to make any clarifications.
  Any ideas?

• NikosV

  SSCrazy

  Points: 2111

  More actions

  September 20, 2018 at 1:52 am

  #2006391

  Added AND TargTable.ReportMonth = @month


  New Code
  WHEN NOT MATCHED BY SOURCE AND TargTable.ReportMonth = @month
THEN
DELETE;