How to show column that have updated value? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2000 Forums

Transact-SQL (2000)

How to show column that have updated value?

Author

Topic

aex
Yak Posting Veteran

60 Posts

Posted - 2006-06-07 : 13:49:45

I have two tables and the structure and value is as below:

Table_A
id col1 col2
1   10   0

Table_B
id col1 col2
1   10   20

From the tables above, I want to show the column that has updated value only. The result I want is as shown below (note that col1 is not shown because Table_A.col1 = Table_B.col1).

Result
id col2
1   20

Can this be done using tsql?

Thanks for any reply.

aex

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-06-07 : 13:57:03

It can be done, but you'll need lots of queries.

SELECT b.id, b.col2
FROM Table_B b
INNER JOIN Table_A a
ON b.id = a.id
WHERE b.col2 <> a.col2

SELECT b.id, b.col1
FROM Table_B b
INNER JOIN Table_A a
ON b.id = a.id
WHERE b.col1 <> a.col1

...

Tara Kizer
aka tduggan

cmdr_skywalker
Posting Yak Master

159 Posts

Posted - 2006-06-07 : 15:23:04

or you can place all the column in one select using case statement
SELECT
a.id,
CASE
WHEN a.col1 <> a.col1 THEN b.col1 --use ISNULL(a.col1, -1) if you need to
ELSE
NULL
END as [col1],
CASE
WHEN a.col2 <> b.col2 THEN b.col2
ELSE
NULL
END as [col2]
FROM Table_B b INNER JOIN Table_A a
ON b.id = a.id

May the Almighty God bless us all!

aex
Yak Posting Veteran

60 Posts

Posted - 2006-06-07 : 23:07:25

Thanks a lot every one. Thanks for the suggestion. I will try the suggestion that that you guys gave. In fact, what I am trying to do is to compare the data between two tables Table_A and Table_B (If there is any particular cell in Table_B is updated, then update to Table_A accordingly).

My self-experiment is that i try to calculate the executing time of this kind of operation. With my computer (AMD 1.0Ghz processor, 1GB RAM), if i am going to compare all the table cell in Table_B against Table_A, and if both table consist of 60 fields and 10000 records, and my calculation shows that, the operation will be completed fully in 83 days. Just imagine how tedious the task is. This is why I am looking a way to get columns that have updated value only for a particular row.

aex

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources