Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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.

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

Total article views: 1962 | Views in the last 30 days: 5
 
Related Articles
FORUM

Sql server computed field

Create Sql server computed field

FORUM

Sql server computed field

Create Sql server computed field

FORUM

Checksum function???

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

BLOG

SSIS – CHECKSUM Transformation

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

FORUM

Torn_page_detection Vs Checksum (SQL Server 2005 database upgrade)

Torn_page_detection Vs Checksum (SQL Server 2005 database upgrade)

Tags
checksum    
replication    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones