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.
USAGE:
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.
Kurt
