• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1223
  • Last Modified:

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
0
CLoucas
Asked:
CLoucas
4 Solutions
 
psychic_zeroCommented:
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
 
appariCommented:
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]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
 
chapmandewCommented:
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now