Finding differences between records in two tables

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
CLoucasAsked:
Who is Participating?
 
psychic_zeroConnect With a Mentor Commented:
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
 
CLoucasAuthor Commented:
Thanks for your reply....I would like to check for more than one field though....
0
 
appariConnect With a Mentor Commented:
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
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
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
 
chapmandewConnect With a Mentor Commented:
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
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.