Solved

T-SQL: Comparing 2 tables

Posted on 2007-12-04
3
2,236 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 500 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
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.
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

630 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