Merge statement - matching ON all columns

  • Dear SQL users,

    I have a need to refresh tables from Source to Target.  there is no particular 'key' or combination of 'key-ish' values for my ON portion.  All I am doing in lieu of that is to match on all columns.  on sourcetable.col1 = targettable.col1 and sourcetable.col2 = targettable.col2  and so on and so forth.  There are about 25 columns.  It is possible that at least some of these columns could have the Null value.

    Is my merge statement doomed?

  • If there are no updates (impossible without a key) or deletes, I would suggest not using MERGE, but INSERT:

    INSERT target (cols)
    SELECT cols FROM source
    WHERE NOT EXISTS (SELECT source.cols INTERSECT target.cols)

    the INTERSECT handles NULLs

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • This is what I typically end up with. It handles either column being null.

      WHEN MATCHED AND (IIF(T.Site_Name IS NULL, IIF(S.Site_Name IS NULL, 0, 1), IIF(S.Site_Name IS NULL, 1, IIF(T.Site_Name = S.Site_Name, 0, 1))) = 1 OR 
    IIF(T.Site_Address_1 IS NULL, IIF(S.Site_Address_1 IS NULL, 0, 1), IIF(S.Site_Address_1 IS NULL, 1, IIF(T.Site_Address_1 = S.Site_Address_1, 0, 1))) = 1 OR
    IIF(T.Site_Address_2 IS NULL, IIF(S.Site_Address_2 IS NULL, 0, 1), IIF(S.Site_Address_2 IS NULL, 1, IIF(T.Site_Address_2 = S.Site_Address_2, 0, 1))) = 1 OR
    IIF(T.Town IS NULL, IIF(S.Town IS NULL, 0, 1), IIF(S.Town IS NULL, 1, IIF(T.Town = S.Town COLLATE DATABASE_DEFAULT, 0, 1))) = 1 OR
    IIF(T.County IS NULL, IIF(S.County IS NULL, 0, 1), IIF(S.County IS NULL, 1, IIF(T.County = S.County, 0, 1))) = 1 OR
    IIF(T.Postal_Code IS NULL, IIF(S.Postal_Code IS NULL, 0, 1), IIF(S.Postal_Code IS NULL, 1, IIF(T.Postal_Code = S.Postal_Code, 0, 1))) = 1 OR
    IIF(T.Status IS NULL, IIF(S.Status IS NULL, 0, 1), IIF(S.Status IS NULL, 1, IIF(T.Status = S.Status, 0, 1))) = 1) THEN
  • Phil Parkin wrote:

    If there are no updates (impossible without a key) or deletes, I would suggest not using MERGE, but INSERT:

    INSERT target (cols)
    SELECT cols FROM source
    WHERE NOT EXISTS (SELECT source.cols INTERSECT target.cols)

    the INTERSECT handles NULLs

    I prefer EXCEPT here.

    INSERT target (cols)
    SELECT cols FROM source
    EXCEPT
    SELECT cols FROM target

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • just thinking outside the box

    Because you have no key then replication is out of the game

    could you move the tables you need to a new filegroup? that gives you lots of options regarding restoring just that filegroup without having to do a merge

    MVDBA

  • This was removed by the editor as SPAM

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

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