Solved

Finding differences between records in two tables

Posted on 2008-06-24
5
1,197 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
5 Comments
 
LVL 6

Accepted Solution

by:
psychic_zero earned 125 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 125 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 125 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 125 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

749 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