Our team was recently faced with an integration challenge involving a syncing multiple SQL databases. These databases need to be updated regularly and serve as a data caching layer of sorts. One of the solutions we looked at involved writing a set of stored procedures which would look at the current data and compare it with the new data to find the deltas.
In the past, this was typically accomplished by writing complex T-SQL which typically threw caution to the wind as far as performance was concerned. We were hestiant to go that path but thought it warranted at least some consideration. Luckily, one of our team members came upon the MERGE command in SQL Server 2008. Essentially, the MERGE command allows you to write much cleaner T-SQL which compares dataset A with dataset B and allows you to take different actions depending on your data logic. I’ll cover the syntax in more detail in a later post.
Most of the examples and tutorials using the MERGE command on the web only demonstrate merging among tables on the same database. Our challege involved merging across databases. Thankfully, merging across different databases is supported!
To demonstrate, I created two databases – a staging and a live. The staging would act as the source of the data and the live database would obviously be the final, synced version. After merging, the data in the two databases should be identical.
Not surprisingly, the only requirement when merging across databases with the MERGE command is to use the fully qualified database name.
Hope this helps!