February 25, 2020 at 6:42 pm
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?
February 25, 2020 at 7:33 pm
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
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 27, 2020 at 12:50 pm
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
February 27, 2020 at 3:48 pm
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
February 27, 2020 at 4:05 pm
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
March 4, 2020 at 9:42 am
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