Post reply

Avoiding Multiple Merges

  • November 25, 2014 at 8:10 am

    #397688

    Hi All,

    Very new to SQL and SQL Server. But it seems that doesn't prevent work from being assigned 😎

    SO, I'm trying to implement a manual adjustment process using principles of SCD2 - that is, when a manual adjustment file is sent, load that file into a table and then compare this table to the central fact table. When matched and is.latest = 'Y' then insert blah blah.

    Now, the fact will, be fed from multiple source tables and each of these sources have their own primary keys. So, in essence, to apply the adjustments I have to do multiple merges against each source :

    1st Merge for 1st source:

    merge fact as dst

    using sourcetable1 as src

    on src.pk1 = dst.pk1

    when matched

    .....

    merge2 for source2:

    merge fact as dst

    using sourcetable1 as src

    on src.pk2 = dst.pk1

    when matched

    .....

    and so on.

    Now, I know that for records from say, sourcetable1, pk2,pk2 etc. will be all, null. Only pk1 will be populated. Using this knowledge, I want to do something like:

    case when fact.pk1 is not null and fact.pk2 is null and fact.pk3 is null and .....

    merge fact as dst

    using sourcetable1 as src

    on src.pk2 = dst.pk1

    when fact.pk2 is not null and fact.pk1 is null and fact.pk2 is null

    and so on. This is in a bid to avoid about 9 different merge statements to satisfy my need for manual adjustments for 9 sources

  • November 25, 2014 at 8:40 am

    #1760843

    forgot to ask...seeking help from others on how to implement the logic please.

  • November 25, 2014 at 10:31 am

    #1760878

    I don't think you need case in merge

    Just like in the join you can add additional conditions and you can have multiple WHEN (NOT) MATCHED conditions

    WHEN MATCHED AND (src1.col1=NULL AND src1.col2 IS NOT NULL) THEN

    WHEN MATCHED AND (src1.col1 IS NOT NULL AND src1.col2=NULL) THEN

    WHEN NOT MATCHED AND src1.col1=NULL THEN

    WHEN NOT MATCHED BY SOURCE AND (src1.col1=NULL AND src1.col2=NULL) THEN

    Alex S

  • November 25, 2014 at 10:53 am

    #1760881

    Thanks Alex. Appreciate your response.

    Question - to initiate the merge, I'll need the ON statement. The ON statement itself will need to have the key I'm merging on. Sorry, if I'm not being able to ask the question correctly. Let me try to write a pseudo code:

    merge fact as dst

    using table as src

    on dst.pk = src.pk

    case pk

    when fact.pk1 is not null then pk1

    when fact.pk2 is not null then pk2

    .....

  • November 25, 2014 at 11:32 am

    #1760889

    You can't use case statement in merge the way you described.

    Alex S

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

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