Selenium WebDriver Java Framework Course Limited Time Offer for $20

Selenium WebDriver Java Framework Course Limited Time Offer for $20

 

Print

Compare Tables in SQL Server Database

When testing database tables in SQL server, sometimes, we need to do A B testing. Basically, we need to compare table A with Table B to find out common records or differences.

In SQL statement, we can use INTERSECT to find common rows between two tables. 

 

In SQL statement, we can use EXCEPT to find different rows between two tables.

Use AdventureWorks2008
Go
-- Select top 10 persons from Person Table and store it as temp table #a
print 'Select top 10 persons from Person Table and store it as temp table #a'
select Distinct top 10 FirstName,MiddleName,LastName into #a from Person.Person

--Select top 5 persons from Person Table and store it as temp table #b
print 'Select top 5 persons from Person Table and store it as temp table #b'
select Distinct top 5 FirstName,MiddleName,LastName into #b from Person.Person
--Display 10 records from #a
print '10 records in table #a'
select * from #a

--Display 5 records from #b
print '5 records in tabel #b'

select * from #b

Query Result (Display records in table #a and #b)

(10 row(s) affected)
Select top 5 persons from Person Table and store it as temp table #b

(5 row(s) affected)
10 records in table #a
FirstName                                          MiddleName                                         LastName
-------------------------------------------------- -------------------------------------------------- --------------------------------------------------
Syed                                               E                                                  Abbas
Catherine                                          R.                                                 Abel
Kim                                                NULL                                               Abercrombie
Kim                                                B                                                  Abercrombie
Hazem                                              E                                                  Abolrous
Sam                                                NULL                                               Abolrous
Humberto                                           NULL                                               Acevedo
Gustavo                                            NULL                                               Achong
Pilar                                              NULL                                               Ackerman
Pilar                                              G                                                  Ackerman

(10 row(s) affected)

5 records in tabel #b
FirstName                                          MiddleName                                         LastName
-------------------------------------------------- -------------------------------------------------- --------------------------------------------------
Syed                                               E                                                  Abbas
Catherine                                          R.                                                 Abel
Kim                                                NULL                                               Abercrombie
Kim                                                B                                                  Abercrombie
Hazem                                              E                                                  Abolrous

(5 row(s) affected)

Compare two tables

--Find common records between #a and #b (use INTERSECT)(common records should be the 5 records in table #b)

print 'common records should be the 5 records in table #b'

select * from #a 
intersect
select * from #b

--Find the records in #a that do not exist in #b (use EXCEPT)(table #a has 5 records that do not exist in table #b)
print 'table #a has 5 records that do not exist in table #b'

select * from #a
Except
select * from #b

--Find the records in #b that do not exist in #a (use EXCEPT)(table #b does not have any records that do not exist in table #a) 

print 'table #b does not have any records that do not exist in table #a'

select * from #b
Except
select * from #a

--Delete temporary tables

drop table #a,#b

Query Result

common records should be the 5 records in table #b
FirstName                                          MiddleName                                         LastName
-------------------------------------------------- -------------------------------------------------- --------------------------------------------------
Catherine                                          R.                                                 Abel
Hazem                                              E                                                  Abolrous
Kim                                                NULL                                               Abercrombie
Kim                                                B                                                  Abercrombie
Syed                                               E                                                  Abbas

(5 row(s) affected)

table #a has 5 records that do not exist in table #b
FirstName                                          MiddleName                                         LastName
-------------------------------------------------- -------------------------------------------------- --------------------------------------------------
Gustavo                                            NULL                                               Achong
Humberto                                           NULL                                               Acevedo
Pilar                                              NULL                                               Ackerman
Pilar                                              G                                                  Ackerman
Sam                                                NULL                                               Abolrous

(5 row(s) affected)

table #b does not have any records that do not exist in table #a
FirstName                                          MiddleName                                         LastName
-------------------------------------------------- -------------------------------------------------- --------------------------------------------------

(0 row(s) affected)