Solved

Finding differences between records in two tables

Posted on 2008-06-24
5
1,209 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 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

729 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