HASH/Compare

Should be a simple question but I've never done it before and would love a little help getting started.  I have a demographic table with about 30 fields.  On a daily basis, I get a new file with potentially updated data.  Whats the simpliest way to do a comparison to see if any of the 30 field have changed?  I know I could do a series of WHERE newtable.field1<>existingtables.field1 but that has to be an easier way.

suggestions?

Thanks in advance!!
gmastervAsked:
Who is Participating?
 
baretreeConnect With a Mentor Commented:
just like lwadwell showed

SELECT  *
FROM     newTable
EXCEPT
SELECT  *
FROM     existingTable

you can insert the result into a temp table with same structure like # or @ tables

create table #tempTable (accountNumber type, address type, city type, etc )

insert into #tempTable
SELECT  *
FROM     newTable
EXCEPT
SELECT  *
FROM     existingTable

select accountNumber from #tempTable

drop table #tempTable

you can use a @ table like

declare  @tempTable table (accountNumber type, address type, city type, etc )

and avoid the "drop table" statement :)

0
 
lwadwellCommented:
assuming all of the columns are in the same order in the existing and new tables ... try:

SELECT *
FROM newtable
MINUS
SELECT *
FROM existingtable
0
 
gmastervAuthor Commented:
they willl/can be exactly the same but I forgot to mention, what I want to do is compare it by account number (primary key in both tables) and see if any other field (address, city, st, zip, phone, etc) has changed.  If so, I just need to know what accounts have updated records.  Does this solution still work for that?  how would I write the join statement if so?  Thanks and my apologies for the lack of detail in my original question.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
baretreeCommented:
being using MS SQL then you need to use EXCEPT instead since MiNUS is an Oracle statement
you can also use the NOT iN but EXCEPT is "newer"...


0
 
gmastervAuthor Commented:
thank you, can you help me with the join statement?  will that work?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
suggestion would indeed to compare the hashed value (on all the data fields), which on the existing table could be a computed field ...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.