2018-06-04

Manually syncing data from an external data source

In our environment, we have several linked servers defined for our MS SQL central DB. One or two times a day we need to pull data from these external data sources and store it in a local table for further processing. All records have timestamps, but we cannot rely on time being in sync between the MS SQL and linked servers. Moreover, some records can be recorded at a later time, yet having earlier timestamps.
So, here what we did to come up with a workable solution in these constraints. First, we use the current time offset back as a parameter to call to a linked server. The offset is sufficient enough to guarantee we will get all the records we did not see before. Second, we'll use EXCEPT to skip the records we already have in the local table. Here's the gist:

Another point worth to mention, yes, you can use insert ... Exec sp_executesql @sql_string to insert results of stored procedure execution into a table.

No comments: