SQL Server: Comparing data during a test cycle with the T-SQL EXCEPT command

Hi Folks,

Sometimes I am developing and need to compare output of data after making changes to a system that updates or inserts data into a table. What I usually do is have a production test batch of records in a table, then have my development batch, when I make new releases I compare the Production Batch with the Development Batch, to ensure the "New Features" I implemented do not affect the production rules.

Ok, so let’s say we have this table:

 

Now I populate it with production data:

insert into dbo.FunctionTests(Name,Surname,Environment)

values (‘Romiko’, ‘van de dronker’,‘PROD’)

insert into dbo.FunctionTests(Name,Surname,Environment)

values (‘Bob’, ‘The Builder’,‘PROD’)

 

Let’s imagine I wrote some code to make these results from a web form or so, and I use the same form in my development environment, but I introduced a bug, where Bob The Builder’s name was spelt incorrectly, lets add a ‘S’ to it in the Dev environment:

insert into dbo.FunctionTests(Name,Surname,Environment)

values (‘Romiko’, ‘van de dronker’,‘DEV’)

insert into dbo.FunctionTests(Name,Surname,Environment)

values (‘Bob’, ‘The BuilderS’,‘DEV’)

Notice the S.

 

So what we want is a query to show the difference between the batches:

select Name,Surname

from dbo.FunctionTests

where Environment = ‘PROD’

EXCEPT

select Name,Surname

from dbo.FunctionTests

where Environment = ‘DEV’

 

select Name,Surname

from dbo.FunctionTests

where Environment = ‘DEV’

EXCEPT

select Name,Surname

from dbo.FunctionTests

where Environment = ‘PROD’

 

And here are the results, it only shows the changes.

 

Hope you find this useful for your functional tests and need to compare results!

Cheers.

Advertisements
Uncategorized

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