Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

T-SQL: Comparing 2 tables

Posted on 2007-12-04
3
Medium Priority
?
2,237 Views
Last Modified: 2012-05-05
when comparing 2 tables, is there a way to return the spicific column that changed?
Currently I am successfully comparing the tables using this code:
SELECT MIN(TableName) AS TableName, RateNumber, UpdatedBy, UpdatedOn
	FROM
	(
 
	SELECT 'Current Data' AS TableName, tar_number AS RateNumber, tar_updateby AS UpdatedBy, tar_updateon AS UpdatedOn
	FROM tariffheader
 
	  UNION ALL
 
	SELECT 'Original Snapshot' AS TableName, RateNumber, UpdatedBy, UpdatedOn
	FROM TarrifSnapshotTemp
	) tmp
 
	GROUP BY RateNumber, UpdatedBy, UpdatedOn
	HAVING COUNT(*) = 1
	ORDER BY RateNumber

Open in new window

0
Comment
Question by:pablo_munoz
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 25

Accepted Solution

by:
imitchie earned 2000 total points
ID: 20406268
SELECT MIN(TableName) AS TableName, RateNumber, UpdatedBy, UpdatedOn,
 case when min(RateNumber) <> max(RateNumber) then 'YES' else '' end as RateNumberChanged,
 case when min(UpdatedBy) <> max(UpdatedBy) then 'YES' else '' end as UpdatedByChanged,
 case when min(UpdatedOn) <> max(UpdatedOn) then 'YES' else '' end as UpdatedOnChanged
        FROM
        (
 
        SELECT 'Current Data' AS TableName, tar_number AS RateNumber, tar_updateby AS UpdatedBy, tar_updateon AS UpdatedOn
        FROM tariffheader
 
          UNION ALL
 
        SELECT 'Original Snapshot' AS TableName, RateNumber, UpdatedBy, UpdatedOn
        FROM TarrifSnapshotTemp
        ) tmp
 
        GROUP BY RateNumber, UpdatedBy, UpdatedOn
        HAVING COUNT(*) = 1
        ORDER BY RateNumber
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20406296
Actually, can you please explain
"currently I am successfully comparing the tables using this code"

What is it successfully doing? It looks like it's showing you the rows that exist only in Current or Shapshot.  Is there an ID that links them together?
0
 
LVL 3

Author Comment

by:pablo_munoz
ID: 20406354
Actually it's the same data, I don't have the ability to add a trigger to the table that I need to check, so instead I am selecting the data into a temp table, once I have a snapshot of the current data, I can then compare the two tables and check for differences. if there is a difference, I email the concerned party. I also update the temp table if there is a difference.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question