Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Data Comparison SQL

Posted on 2012-08-14
2
Medium Priority
?
905 Views
Last Modified: 2012-08-15
Hi Experts,

Today I have the task of comparing data from two tables, this has been the process so far.

Write SQL Select for data source
Write SQL Select for data target

So I have two tables, table A and Table B.

They have the same amount of columns but they have different names (at this point).

What technique is recommended for this process of collecting records which match?

Thank you
0
Comment
Question by:BIT_BOPPER
2 Comments
 
LVL 11

Expert Comment

by:Lara F
ID: 38294593
You need to know on what column(s) you need to compare, and if columns can have nulls.

here is comparison based on 2 columns that do not allow nulls

-- data present in both tables
select a.*, b.*
from A join B on a.ac1=b.bc1 and a.ac2=b.bc2
-- data present in table a only
select a.*, b.*
from A
left join B on a.ac1=b.bc1 and a.ac2=b.bc2
where b.bc1 is null and b.bc2 is null

-- data present in table B only
select a.*, b.*
from B
left join A on a.ac1=b.bc1 and a.ac2=b.bc2
where a.ac1 is null and a.ac2 is null
0
 
LVL 25

Accepted Solution

by:
lwadwell earned 2000 total points
ID: 38294923
You might be able to use INTERSECT to find the rows in common between both tables

SELECT * FROM table_A
INTERSECT
SELECT * FROM table_B

ref: http://msdn.microsoft.com/en-us/library/ms188055(v=sql.100).aspx
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

810 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