The code contains a stored procedure and code to show how it works aftwerwards.
PURPOSE: I need to sync data between two databases. One database resides on a computer without an internet connection.
The plan is to create a text file containing information from the isolated computer's SQL-Express database that can be put on a flash drive. This procedure will provide that data. Once the user is at a internet-ready machine, another application can use the text document to pull down only changes from a website that has the master database. In my case, data goes one direction: to the isolated computer's database. If anything needs to ship the other direction, the user specifically selects it.
THEORY: The procedure builds a custom query which will group on the primary key, and create a checksum of all fields we care about.
The Checksum is created
- for each row: combining all fields into a text string and doing a HASHBYTES on that
- sum parts of the checksum into one bigint
You're welcome offer comments on it's design and thoughts about how unique the hash may end up being.
Should be easy to see how to use the procedure. The procedure expects
- @TableName can be a temp table or table in another database
- @PrimaryKey data type is flexible. You could even pass it two fields if needed, as in 'HalfAUniqueKey, OtherHalfOfUniqueKey'
- @FieldList list of fields (normally separated by a semicolon) that we care to check for changes. Data types can be mixed. I've tested it on numeric and some text fields.
- @FieldListDelimiter optional..you see the default is a semicolon
- @NullText optional..varchar of something the database will never see in reality
- @DEBUG optional..if set to 1, will print the SQL code rather than running it
The procedure returns a table, which can then be linked to other tables in the database for further processing. In my case, I link it to the table with the primary keys, and pull out the human-readable description rather than the ID.
Again, feel free to offer comments.