What's the best way to compare two tables?

Question

What's the best way to compare two tables?

WayneS

SSC Guru

Points: 95460
More actions
April 17, 2009 at 8:29 am

#133183

I'm working on testing out some modifications to a procedure. To do this, I've created to databases. The first one runs the original procedure. The second one runs the new one.
Now, I need to compare the results of a table between the two databases. The uniqueness of a row in this table is spread out across 6 columns. The PK on this table is an identity column... so unless I can guarantee that the rows will be inserted in the same order (at this point, I can't), then I can't use this to compare.
So, is the way I'm doing it (below) the best way? Or can someone come up with a better way?
EDIT: Note that the unique columns are not all of the columns in the tables.
Thanks,
Wayne
if OBJECT_ID('tempdb..#test') is not null drop table #test
if OBJECT_ID('tempdb..#test2') is not null drop table #test2
create table #test (student_id int, term_nm varchar(6), school_yr_dt char(4), dir_sch_cd char(6), rotc_sch_cd char(6), obligation_cd char(3), modified_dt datetime, rowguid uniqueidentifier)
create table #test2 (student_id int, term_nm varchar(6), school_yr_dt char(4), dir_sch_cd char(6), rotc_sch_cd char(6), obligation_cd char(3), modified_dt datetime, rowguid uniqueidentifier)
CREATE UNIQUE INDEX [ix_test_cover] ON #test (student_id, term_nm, school_yr_dt, dir_sch_cd, rotc_sch_cd, obligation_cd)
CREATE UNIQUE INDEX [ix_test2_cover] ON #test2 (student_id, term_nm, school_yr_dt, dir_sch_cd, rotc_sch_cd, obligation_cd)
insert into #test
select 1, 'FALL', '2008', '001155', '001153', 'EST' union
select 1, 'SPRING', '2009', '001155', '001153', 'EST' union
select 2, 'FALL', '2008', '003798', '003798', 'EST' union
select 3, 'FALL', '2008', '003578', '003578', 'EST'
insert into #test2
select 1, 'FALL', '2008', '001155', '001153', 'EST' union
select 2, 'FALL', '2008', '003798', '003798', 'EST' union
select 3, 'FALL', '2008', '003578', '003578', 'EST' union
select 3, 'SPRING', '2009', '003578', '003578', 'EST'
-- get records in the new way, but not in the old way
select * from #test
where CONVERT(char(15), student_id) + term_nm + school_yr_dt + dir_sch_cd + rotc_sch_cd + obligation_cd not in (
select CONVERT(char(15), student_id) + term_nm + school_yr_dt + dir_sch_cd + rotc_sch_cd + obligation_cd
from #test2
)
-- get records in the old way, but not in the new way
select * from #test2
where CONVERT(char(15), student_id) + term_nm + school_yr_dt + dir_sch_cd + rotc_sch_cd + obligation_cd not in (
select CONVERT(char(15), student_id) + term_nm + school_yr_dt + dir_sch_cd + rotc_sch_cd + obligation_cd
from #test
)
if OBJECT_ID('tempdb..#test') is not null drop table #test
if OBJECT_ID('tempdb..#test2') is not null drop table #test2
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

Take a look at the "Except" operator in Books Online. It's a pretty slick way to compare the contents of two tables.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

WayneS SSC Guru Points: 95460 More actions · Answer 2

Thanks, that is pretty slick (and pretty fast also).

My actual tables have additional columns (modified date, guid, others... I should have included some of them in the test) ... to show all columns I can't use EXCEPT (there I use just the columns that make up the uniqueness).

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

SQLRocker SSCrazy Points: 2217 More actions · Answer 3

SQLRocker

SSCrazy

Points: 2217

April 17, 2009 at 9:26 am

#979748

if 2005, Have you tried TableDiff.exe?

WayneS SSC Guru Points: 95460 More actions · Answer 4

SQLRocker (4/17/2009)
if 2005, Have you tried TableDiff.exe?

TableDiff compares all columns, and if any are different it shows the differences.

So, every row with different data (the identity column, the modified_dt column, the rowguid column) would make the row show up, even though the other columns may not be different.

I just need to compare the unique columns, but show all columns.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

jbreffni SSC Eights! Points: 949 More actions · Answer 5

use except to find the records you need

wrap that up as a sub query

use the sub query as a filter for the records you want all columns from

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

What do you want as your output, Wayne?

If you want to find out fast if two tables match exactly on a set of columns, then here's a simple place to start:

DROP TABLE #Table1

CREATE TABLE #Table1 (TodayDate DATETIME, [Machine Part] VARCHAR(10), Location VARCHAR(2), [Mfg. Date] DATETIME, AnotherColumn VARCHAR(1))

INSERT INTO #Table1 (TodayDate, [Machine Part], Location, [Mfg. Date])

SELECT '2009-04-16', 'Spindle', 'KS', '2009-04-13' UNION ALL

SELECT '2009-04-16', 'Spring', 'CA', '2009-04-14' UNION ALL

SELECT '2009-04-16', 'Spring', 'NJ', '2009-04-14'

DROP TABLE #Table2

CREATE TABLE #Table2 (TodayDate DATETIME, [Machine Part] VARCHAR(10), Location VARCHAR(2), [Mfg. Date] DATETIME, AnotherColumn VARCHAR(1))

INSERT INTO #Table2 (TodayDate, [Machine Part], Location, [Mfg. Date])

SELECT '2009-04-16', 'Spindle', 'KS', '2009-04-13' UNION ALL

SELECT '2009-04-16', 'Spring', 'CA', '2009-04-14' UNION ALL

SELECT '2009-04-16', 'Spring', 'NJ', '2009-04-14'

SELECT TodayDate, [Machine Part], Location, [Mfg. Date] FROM #Table1

UNION

SELECT TodayDate, [Machine Part], Location, [Mfg. Date] FROM #Table2

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

WayneS SSC Guru Points: 95460 More actions · Answer 7

jbreffni (4/17/2009)
use except to find the records you need
wrap that up as a sub query
use the sub query as a filter for the records you want all columns from

Thanks... my brain had gotten locked into a different way of thinking and it had me convinced I still needed to do that complicated thing of appending the fields together.

I got it now... and much faster than how I had been doing it.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

WayneS SSC Guru Points: 95460 More actions · Answer 8

Chris Morris (4/17/2009)
What do you want as your output, Wayne?

I'm trying to get rows from table1 that are different from table2. I was able to get it through above suggestions... using an except to get the differences, and use that to get all data for the rows that it pulled.

Thanks...

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

WayneS (4/17/2009)

Chris Morris (4/17/2009)
What do you want as your output, Wayne?/quote]
I'm trying to get things from table1 that are different from table2. I was able to get it through above suggestions... using an except to get the differences, and use that to get all data for the rows that it pulled.
Thanks...

Top work, Wayne! Thanks for the feedback.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden