Merge from Production table to UAT table (which has keys).

• komal145

  SSCrazy Eights

  Points: 9918

  More actions

  November 9, 2017 at 9:53 am

  #326160

  I have a daily exchange rate table , which updates the exchange rates daily but this process updates correctly in Production but UAT is not getting updated. For which i need to merge the data from production to UAT.

  I am confused how can i merge production to UAT as the tables have keys and FactID is identity column

  UAT                
  FactID    ExchangeCalendarDateKey    FromCurrencyKey    ToCurrencyKey    ExchangeRate
  904469    20171109    1    1    1
  904468    20171109    1    2    1.158233
  904467    20171109    1    3    1.480618
  904502    20171109    2    1    0.863384
  904503    20171109    2    2    1
  904519    20171109    2    3    1.278342
  904520    20171109    2    4    6.6401
  904537    20171109    3    1    0.675394
  904538    20171109    3    2    0.782263
  904539    20171109    3    3    1
                  
  PRODUCTION                
  FactID    ExchangeCalendarDateKey    FromCurrencyKey    ToCurrencyKey    ExchangeRate
  904469    20171109    1    1    1
  904468    20171109    1    2    1.158233
  904467    20171109    1    3    1.480618
  904502    20171109    2    1    0.863384
  904503    20171109    2    2    1
  904519    20171109    2    3    1.278342
  908529    20171109    2    4    6.628395
  904537    20171109    3    1    0.675394
  904538    20171109    3    2    0.782263
  904539    20171109    3    3    1

  Here :
  FactID is identity column and From currency is coming from Curreny table :

  CurrencyKey    CurrencyName    CurrencyCode    CurrencyDescription
  1    EUR    EUR    Euro
  2    USD    USD    USD
  3    CAD    CAD    CAD
  4    CNY    CNY    CNY

  I checked the Currency table to be same both in Prod and UAT. Just wante dot make sure the keys should not differ if i do merge on the keys.

  My pacakge:
  1) Got data from production to Stage table in UAT.
  2) After getting to UAT, I jsut used the merge statement from Stage to UAT update.

  CREATE PROCEDURE [dw].[Merge_DailyExchangeRates]
  AS
  BEGIN
  SET NOCOUNT ON;

  MERGE [DW].[fact].[DailyExchangeRate] T
  USING [DW].[Stage].[Merge_DailyExchangeRate] S
  ON T.Exchangecalendardatekey=S.Exchangecalendardatekey
  and T.Fromcurrencykey=S.Fromcurrencykey
  AND T.Tocurrencykey=S.toCurrencykey
  WHEN MATCHED THEN
    UPDATE
    SET
     T.ExchangeRate=S.ExchangeRate
  WHEN NOT MATCHED BY TARGET THEN
    INSERT([ExchangeCalendarDateKey], [FromCurrencyKey], [ToCurrencyKey],[ExchangeRate],[CtrlLoadDate],[CtrlETLExecID])
    VALUES(S.[ExchangeCalendarDateKey], S.[FromCurrencyKey], S.[ToCurrencyKey],S.[ExchangeRate],S.[CtrlLoadDate],S.[CtrlETLExecID]);

  END

  IS this right way to do? Please suggest. If not any other wya to do it properly?

• Phil Parkin

  SSC Guru

  Points: 246978

  More actions

  November 9, 2017 at 11:20 am

  #1967436

  This looks like the best approach to me.
  To avoid wasting time updating rows which have not changed, I'd suggest using

  ...WHEN MATCHED AND NOT EXISTS
       (
         SELECT
          s.ExchangeRate
         INTERSECT
         SELECT
          t.ExchangeRate
       ) THEN
  UPDATE SET ...

  I'd also suggest using
  MERGE fact.DailyExchangeRate WITH (HOLDLOCK) t
  to deal with some of the possible issues with MERGE.
  Finally, if your proc is in database DW, it is bad practice to use the DW prefix for referencing objects within that database, so I suggest that you use two-part object names only.

  The absence of evidence is not evidence of absence.
  Martin Rees

  You can lead a horse to water, but a pencil must be lead.
  Stan Laurel