Microsoft – SQLServer – TableDiff

Background

Reviewing lots of Stored Procedures lately and some of them perform quite a bit of ETL operations.  As we make the changes knew that we need to make sure that our changes essentially yield the same data in the resultant tables.

 

LAB

DDL

Let us create our sample tables.

The tables will be named courseLegacy and courseRevised and they will share the course Schema.

They will have two columns courseID and courseName.  The courseID will be our primary key and a unique index will be placed on the courseName, as well.

 


if db_id('DBLAB') is null
begin

    exec('create database [DBLAB]')

end
go

use [DBLAB]
go

if schema_Id('Academia') is null
begin

    exec('create schema [Academia] authorization [dbo]')

end
go

set noexec off
go

/*
    drop table [Academia].[courseLegacy]
    drop table [Academia].[courseRevised]
*/
if object_id('[Academia].[courseLegacy]') is not null
begin

    set noexec on

end

create table [Academia].[courseLegacy]
(

      [courseID] nvarchar(20)
    , [courseName] nvarchar(100)

    , constraint PK_Academia_CourseLegacy
        primary key
        (
            [courseID]
        )

    , constraint unique_Academia_CourseLegacy_CourseName
        unique
        (
            [courseName]
        )

)
go

set noexec off
go

if object_id('[Academia].[courseRevised]') is not null
begin

    set noexec on

end

create table [Academia].[courseRevised]
(
      [courseID] nvarchar(20)
    , [courseName] nvarchar(100)

    , constraint PK_Academia_CourseRevised
        primary key
        (
            [courseID]
        )

    , constraint unique_Academia_CourseRevised_CourseName
        unique
        (
            [courseName]
        )
)
go

set noexec off
go

 

 

DML

Let us add data into the tables.

Took to the Net and courtesy of Google found a good and very usable sets of courses at ” College of St. Scholastica” ( http://www.css.edu/academics/school-of-nursing/undergraduate-areas-of-study/traditional-nursing-track/current-students.html ).

 


set nocount on
go

truncate table [Academia].[courseLegacy]
go

insert into [Academia].[courseLegacy]
([courseID], [courseName])
values
 ( 'BIO 1036', 'Biology of the Cell')
 ,( 'BIO 2020', 'Microbiology')
 ,( 'BIO 2110', 'Anatomy and Physiology I')
 ,( 'CTA 1102', 'Human Communication')
 ,( 'DGN 1101', 'The Responsible Self')
 ,( 'DGN 1102', 'And Dignity for All')
 ,( 'EGN 1110', 'ENG 1110')
 ,( 'NSG 2200', 'ENG 2200')
 ,( 'NSG 3200', 'Holistic Approach/Hlth Transis')
 ,( 'NSG 3225', 'Hlth Transitions - Holistic')
 ,( 'NSG 3300', 'Holistic Nursing Approaches')
 ,( 'NSG 3325', 'Acute and Chronic Illness')
 ,( 'NSG 3335', 'Evidence Based Practice')
 ,( 'NSG 4200', 'Holistic NSG Approaches')
 ,( 'NSG 4225', 'Multi-System Illness - Holistic Nursing Interventions')
 ,( 'NSG 4235', 'Public Health Nursing')
 ,( 'NSG 4240', 'Community as Client')
 ,( 'NSG 4343', 'NSG Leadership & Management')
 ,( 'NSG 4500', 'Transition into Professional Nursing Practice')
 ,( 'PSY 2208', 'Lifespan Developmental PSY')
 ,( 'PSY 3341', 'Introduction to Counseling')
 ,( 'SOC 2433', 'The Family and Society')
go

truncate table [Academia].[courseRevised]
go

insert into [Academia].[courseRevised]
([courseID], [courseName])
select [courseID], [courseName]
from [Academia].[courseLegacy]

 

In the example above using SQL insert statements added data into [Academia].[courseLegacy]. Once all our data are added, made a copy of same data into
[Academia].[courseRevised].

 

 

Compare Data

Using TableDiff from the command line, let us compare the data


rem db server is loal named instance SQLEXPRESS_V2014
set DBSERVER=.\SQLEXPRESS_V2014

rem db name is DBLAB
set DBNAME=DBLAB

rem write DDL Changes to bring revised table inline
set TransactFileDMLChanges=courseRevisedDMLChanges.sql

rem version specific path to tablediff
set TableDiffPath=C:\Program Files\Microsoft SQL Server\120\COM\

rem if file exist, remove it
IF EXIST %TransactFileDMLChanges% del /F %TransactFileDMLChanges%

rem tablediff
"%TableDiffPath%\tablediff.exe" -sourceserver %DBSERVER% -sourcedatabase %DBNAME% -sourceTable "courseLegacy" -sourceschema Academia -destinationserver %DBSERVER% -destinationdatabase %DBNAME% -destinationTable "courseRevised" -destinationschema Academia -f %TransactFileDMLChanges%

 

Results


Microsoft (R) SQL Server Replication Diff Tool
Copyright (c) 2014 Microsoft Corporation

User-specified agent parameter values:
-sourceserver .\SQLEXPRESS_V2014
-sourcedatabase DBLAB
-sourceTable courseLegacy
-sourceschema Academia
-destinationserver .\SQLEXPRESS_V2014
-destinationdatabase DBLAB
-destinationTable courseRevised
-destinationschema Academia
-f courseRevisedDMLChanges.sql

Table [DBLAB].[Academia].[courseLegacy] on .\SQLEXPRESS_V2014 and Table [DBLAB].
[Academia].[courseRevised] on .\SQLEXPRESS_V2014 are identical.
The requested operation took 0.156009 seconds.

So everything is good.

DML -2

Let us change our data a weeny bit.


set nocount on
go

insert into [Academia].[courseLegacy]
([courseID], [courseName])
values
 ( 'LEGACY-ADD-1', 'Added to Legacy-1')

insert into [Academia].[courseRevised]
([courseID], [courseName])
values
 ( 'Revised-ADD-1', 'Added to Revised-1')

update [Academia].[courseRevised]
set courseName = 'Modern Family and Society'
where courseName = 'The Family and Society'

go

 

Compare Data

Let us go back and re-compare our tables.

 

Results


Microsoft (R) SQL Server Replication Diff Tool
Copyright (c) 2014 Microsoft Corporation

User-specified agent parameter values:
-sourceserver .\SQLEXPRESS_V2014
-sourcedatabase DBLAB
-sourceTable courseLegacy
-sourceschema Academia
-destinationserver .\SQLEXPRESS_V2014
-destinationdatabase DBLAB
-destinationTable courseRevised
-destinationschema Academia
-f courseRevisedDMLChanges.sql

Table [DBLAB].[Academia].[courseLegacy] on .\SQLEXPRESS_V2014 and Table [DBLAB]
[Academia].[courseRevised] on .\SQLEXPRESS_V2014 have 3 differences.
Fix SQL written to courseRevisedDMLChanges.sql.
Err courseID Col
Src. Only N'LEGACY-ADD-1'
Dest. Only N'Revised-ADD-1'
Mismatch N'SOC 2433' courseName
The requested operation took 0.1620093 seconds.

 

Explanation

Here are our changes:

  • The key column is the CourseID
  • LEGACY-ADD-1 :- This is the record we added to the legacy table
  • REVISED-ADD-1 :- This is the record we added to the revised table
  • SOC 2433 – This is the record where we changed the course name

 

Conclusion

This is a relatively easy use case for this high performant tool.

In a future post, we will revisit and talk more into a roadblock that I encountered and like a stumbling sailor home did not materialize.

 

Listening

On a rainy week the words of Denzel Washington echoes a bit:

You pray for rain, you gotta deal with the mud too. That’s a part of it.

And, nothing is any easier to listen to than Carly Simon ( and James Taylor):

Carole King & James Tyalor – So Far Away
https://www.youtube.com/watch?v=urt2cy7AqFs

 

References

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s