June 26, 2008 at 7:39 am
Hi there!!
I have a procedure that compares 2 tables.
insert into TESTE_CasesModificados
select * from BizTalkCaseIntermDetail
where state = 'To Process'
intersect
select * from ActualBizTalk
This statement gives me the info which was altered. But now, I want to know which field was altered. Is there any way to do this or is it impossible?
Thanks.
June 26, 2008 at 12:24 pm
You can always build a set of left outer joins between the two tables. That'll give you which exact column was modified.
Something like:
select t1.Col1
from dbo.Table1 t1
left outer join dbo.Table2 t2
on t1.Col1 = t2.Col1
where t2.Col1 is null
That will give you all the places where there is no match between Col1 in Table1 and Table2. If you have a mutual ID number or other unmodified primary key, you can also include those in the join criteria.
- 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
June 26, 2008 at 2:39 pm
There is no way to do this across all fields without explicitly testing them. It's a pain to build, but if it's needed, you write it once and it's used over and over.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
June 26, 2008 at 8:05 pm
GSquared (6/26/2008)
You can always build a set of left outer joins between the two tables. That'll give you which exact column was modified.Something like:
select t1.Col1
from dbo.Table1 t1
left outer join dbo.Table2 t2
on t1.Col1 = t2.Col1
where t2.Col1 is null
That will give you all the places where there is no match between Col1 in Table1 and Table2. If you have a mutual ID number or other unmodified primary key, you can also include those in the join criteria.
Full outer join whould show both tables...
select t1.Col1 AS t1Col1,
t2.Col2 AS t2Col2
from dbo.Table1 t1
FULL outer join dbo.Table2 t2
on t1.Col1 = t2.Col1
where (t1.Col1 IS NULL
OR t2.Col1 is null)
Of course, you could write a little dynamic SQL to generate that code...
--Jeff Moden
June 27, 2008 at 4:05 am
thanks for the replies.
I will try it. 🙂
June 27, 2008 at 4:29 am
Or you could use:
SQLToolbelt from Redgate
oes allow you to make : database schema differences between 2 databases (or even backups)
and permits you to see the real datadifferences in 2 databases
you could use a free trial or buy a license, is not so expensive if you see what you get in return.
wkr,
eddy
June 27, 2008 at 7:19 am
eddy (6/27/2008)
is not so expensive if you see what you get in return
I've gotta agee there... what you get is proven code with no bugs... Red Gate is real good about that and that code has been around for a long time...
--Jeff Moden
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply