Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Finding differences between records in two tables

Posted on 2008-06-24
5
Medium Priority
?
1,220 Views
Last Modified: 2012-06-27
I have a stored procedure in SQL 2005 that is expected to return the records between two tables that are different.
The T-SQL is :

BEGIN
      SELECT      * FROM tempAssets
      EXCEPT
      SELECT  FROM Assets
END

This returns all non matching records.  However I would like to check differences on specific fields rather than all fields.  Furthermore I would like to have this return the full records.

I would like advise on the T-SQL code to achieve this.  Thanks
0
Comment
Question by:CLoucas
[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
5 Comments
 
LVL 6

Accepted Solution

by:
psychic_zero earned 500 total points
ID: 21863178
Pls try this code, lets say the field name you want to check is AssetID
BEGIN
      SELECT * FROM tempAssets
      WHERE AssetID not in
      ( SELECT AssetID FROM Assets )
END

Open in new window

0
 

Author Comment

by:CLoucas
ID: 21863219
Thanks for your reply....I would like to check for more than one field though....
0
 
LVL 39

Assisted Solution

by:appari
appari earned 500 total points
ID: 21863220
you can try
SELECT      fieldslist FROM tempAssets
      EXCEPT
SELECT fieldslist  FROM Assets

replace fieldslist with the field names you want to compare.
to return full record after compare try this,

Select A.* from
tempAssets A join (
SELECT      fieldslist FROM tempAssets
      EXCEPT
SELECT fieldslist  FROM Assets) B on A.keyCol = B.KeyCol

in field list you have to include the key columns.

0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 500 total points
ID: 21863248
or use a LEFT JOIN
SELECT ta.* 
  FROM tempAssets ta
  LEFT JOIN Assets a
    ON a.somekey = ta.somekey
 WHERE a.somekey IS NULL

Open in new window

0
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 500 total points
ID: 21864569
or a FULL OUTER join

SELECT ta.*, a.*
  FROM tempAssets ta
  FULL OUTER JOIN Assets a
    ON a.somekey = ta.somekey
 WHERE a.somekey IS NULL OR b.somekey IS NULL
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

618 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