November 5, 2009 at 1:01 pm
Hello,
One method that I use to analyze data when some is missing is to compare a database with a previous version of the database. I will left outer join the more complete version of a table from the old database with the current version of the table.
When trying to compare one field with another, I find it helps to have the corresponding fields from the tables listed one after the other. By default, SQL lists all the fields for the joined table after all the fields for the other table. So, for example, I would want the fields listed as:
tbl1.ID, tbl2.ID, tbl1.height, tbl2.height
rather than
tbl1.ID, tbl1.height, tbl2.ID, tbl2.height
It is tedious to have to specify this arrangement of fields manually in a select statement, and I'd imagine that this is a task that people often want to accomplish. Does anyone know of an automated way I can have the joined tables arranged in this way, rather than having to type out the fields each time? I would appreciate any advice that anyone can give. Thanks.
November 5, 2009 at 1:06 pm
I don't know an automatic way to do it, but here's a solution I've used:
select 'tbl1.' + name + ',' as col, name
from sys.columns
where object_id = 50
union all
select 'tbl2.' + name + ',' as col, name
from sys.columns
where object_id = 50
order by name, col
You can, of course, use 3-part names if you need to compare data between tables in two databases, etc.
Edit: Almost forgot: The key is to run this, then copy the first column into your select statement, then remove the last comma.
- 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
November 5, 2009 at 1:14 pm
Add in some nullifs, and it becomes blindingly obvious where the differences are. Try this:
create table #T1 (
ID int identity primary key,
Col1 varchar(10),
Col2 varchar(10),
Col3 varchar(10));
create table #T2 (
ID int identity primary key,
Col1 varchar(10),
Col2 varchar(10),
Col3 varchar(10));
insert into #T1 (Col1, Col2, Col3)
select 'a', 'b', 'c' union all
select 'd', 'e', 'f';
insert into #T2 (Col1, Col2, Col3)
select 'a', 'b', 'c' union all
select 'g', 'e', 'f';
select
T1.ID,
nullif(T1.Col1, T2.Col1) as Col1,
nullif(T1.Col2, T2.Col2) as Col2,
nullif(T1.Col3, T2.Col3) as Col3
from #T1 as T1
inner join #T2 as T2
on T1.ID = T2.ID
where checksum(T1.Col1,T1.Col2,T1.Col3) != checksum(T2.Col1,T2.Col2,T2.Col3);
(Edit for layout and readability.)
- 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
November 5, 2009 at 1:40 pm
Ah, this looks very useful. Thanks very much for your help.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply