Identifying differences between two records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Identifying differences between two records

Author

Topic

Prestidigitator
Starting Member

16 Posts

Posted - 2007-02-08 : 18:52:29

I am trying to test some data handling between two different versions of an application.

I have restored the database schema twice, once as DB_old and once as DB_new.

I import a transaction using the new application into DB_new and I import the SAME transaction into the DB_old using the old version of application.

I then have to eyeball the data in SQL Query Analyzer to try to identify problems where the fields have received different values.

I have done this by running a select statement twice telling it to use both of the databases and then viewing it in two grids. There are a lot of columns so I have to do a lot of scrolling across the screen to do the comparison, and since the view is in two separate grids I have to hop back and forth and click the scroll bars, etc.

It seems like there has to be a better way. I don't suppose there is a way to lock the two grids so they both scroll together is there?

I was thinking maybe I could insert each of the selects into a temporary table and then do some kind of comparison to identify which values were different in each column. Some of the columns will have differences, like the timestamp, but if I could somehow identify which columns were different then I could eyeball them to identify which of those were okay to be different and which of them were actually bugs from the changed application version.

I have no idea how to identify those individual columns with different data values or even where to start.

Just so you understand better what I am doing now here is the query I am running that I then eyeball:

use DB_new
select * from claim  where claim_id = 35144
use DB_old
select * from claim where claim_id = 35144

Thanks for any ideas.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-08 : 19:09:33

http://weblogs.sqlteam.com/jeffs/archive/2004/11/10/2737.aspx

Tara Kizer

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-02-08 : 19:17:14

I just download reg-gate sql compare its 295 i think and its great....i had to compare databases and it did it in seconds...showed logins, view sp you name it ,,,,saved me hours of work.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-08 : 19:20:38

TRACEYSQL, that's different than comparing data. But Red Gate does have a product for that too. There's just no point using software to do it when you can do it easily via Jeff's trick.

Tara Kizer

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-02-09 : 08:10:17

If one table not so bad.....
I used sql compare data aswell its good.

The script looks good i just looked at it,

Prestidigitator
Starting Member

16 Posts

Posted - 2007-02-10 : 01:05:40

quote:
Originally posted by tkizer

http://weblogs.sqlteam.com/jeffs/archive/2004/11/10/2737.aspx

Tara Kizer

That looks interesting - would union work between 2 different databases?

Would I do something like:


(

  SELECT 'database1.Table A' as TableName, A.ID, A.COL1, A.COL2, A.COL3, ...

  FROM A

  UNION ALL

  SELECT 'databse2.Table B' as TableName, B.ID, B.COL1, B.COl2, B.COL3, ...

  FROM B

)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-10 : 01:11:26

You'd reference the other database in the FROM clause using the three part naming convention of the object.

FROM DatabaseName.dbo.TableName

Tara Kizer

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-10 : 02:25:50

quote:
Originally posted by Prestidigitator

quote:
Originally posted by tkizer

http://weblogs.sqlteam.com/jeffs/archive/2004/11/10/2737.aspx

Tara Kizer

That looks interesting - would union work between 2 different databases?

Would I do something like:

(

SELECT 'database1.Table A' as TableName, A.ID, A.COL1, A.COL2, A.COL3, ...

FROM A

UNION ALL

SELECT 'databse2.Table B' as TableName, B.ID, B.COL1, B.COl2, B.COL3, ...

FROM B

)

Prestidigitator, please start a new thread for your question

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources