When creating lookup or reference tables during database design we often run into the problem that the data contained by these tables varies within environments. This can cause problems in the application layer, especially if ENUMS are created based on this table. One of the methods of keeping these tables aligned in a Database Project can be through a Post Deployment script that compares the value of these tables and realign its content. I have seen many database projects with Merge statements, which work very well, but can be challenging to read, especially when the lookup table is not a simple key-pair value table and it contents multiple columns. For example, a table that holds HTML templates for emails or a table that has multiple languages labels.
Most of these merge statements require either a temp table creation or a SELECT statement with unions for each row to create the dataset required for the operation. SQL 2016 and newer versions provide the ability to leverage JSON as an input method, which allows us to declare the dataset needed for the MERGE operator in a more declarative way. This approach requires more lines of code than the traditional MERGE statements but gives the flexibility of avoiding changes on the MERGE statement and only changing a variable to add or remove rows.
Non-JSON Merge statement
JSON Variable Merge Statement
As we can see in the examples above, the JSON version involves the use of OpenJSON to read the JSON variable but allows the content of the statement to be very declarative and easy to read and modify. Creating JSON variables can be made using FOR JSON PATH in SQL server 2016 or newer and formatting can be accomplished with Notepad++ or a code beautifier like codebeautify.org.
SQL Insights 