SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Poor Man's Table Difference

By klini,

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.




Total article views: 1985 | Views in the last 30 days: 2
Related Articles

Sql server computed field

Create Sql server computed field


Sql server computed field

Create Sql server computed field


Checksum function???

How to use Checksum function to compare million records...


SSIS – CHECKSUM Transformation

CHECKSUM is a TSQL function that computes a hash value over a list of arguments.  The great thing ab...


Torn_page_detection Vs Checksum (SQL Server 2005 database upgrade)

Torn_page_detection Vs Checksum (SQL Server 2005 database upgrade)