Sql-server – Shorthand for on primary key

mergeprimary-keysql servert-sql

Is there a shorthand for merging rows based on the primary key? For example:

MERGE INTO [MyDatabase].[MySchema].[MyTable] AS target
USING [MyLinkedServer].[MyDatabase].[MySchema].[MyTable] AS source
   -- The following line is pseudocode illustrating what we want to accomplish
   ON (target primary key equals source primary key)
WHEN MATCHED THEN
    UPDATE SET target.MyColumn = source.MyColumn
WHEN NOT MATCHED BY TARGET THEN
    INSERT (MyCol1, MyCol2, MyCol3)
    VALUES (source.MyCol1, source.MyCol2, source.MyCol3);

Writing out the conditions is tedious if the primary key contains a large number of columns. Also, if the primary key changes, many joins and merges might suddenly break.

You might ask, "Why is the primary key changing?" Real scenario: We were told that an object was uniquely identified by its distinguished name. Later on, we found out that a distinguished name could change if the object moved to another subnet. New requirements rolled in that changed how we uniquely identify the objects. This broke the product in quite a few places. Some of the mess could have been avoided if we had merged on primary key instead of on specific columns.

Best Answer

No there isn't anything similar to what you are asking built into the product.

Standard SQL has the concept of a NATURAL JOIN that joins on common column names but SQL Server does not implement this and there would be no guarantee that any such names would correspond with the PK anyway.

It would be possible in theory for you to write some sort of script pre-processor or dynamic SQL code generator that did this if it was a massively important need for you.

Related Question