[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

comparing content of two tables

Posted on 2012-09-02
6
Medium Priority
?
268 Views
Last Modified: 2012-10-02
I have a table which I copied some time back as a back up.

In the original table some records are removed or changed.

The problem is that IDs of the two tables are different.

Is there a way to compare the records of each table and point out any record is changed or added or removed?

I would know how to write a program and compare the contents but I was wondering if there was a sql command to do that?

thanks

I am using ms sql.
0
Comment
Question by:goodk
  • 2
  • 2
  • 2
6 Comments
 
LVL 25

Assisted Solution

by:lwadwell
lwadwell earned 1000 total points
ID: 38359921
There needs to be a 'key' column (or columns) to be able to join on to do a comparison.  Is there a 'natural' key within the table that can be used?  i.e. a column or group of columns that could be considered as unique/PK.

Otherwise ... if you exclude the ID columns ... a rudimentary set of MINUS would identify rows in 1 table that are not identical in the other ... you need to do this twice e.g.
SELECT col1, col2, col3 ... colx
FROM table1
MINUS
SELECT col1, col2, col3 ... colx
FROM table2

and

SELECT col1, col2, col3 ... colx
FROM table2
MINUS
SELECT col1, col2, col3 ... colx
FROM table1
0
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 1000 total points
ID: 38363971
I think the keyword in ms sql is EXCEPT rather than MINUS. Using the method that lwadwell provided will show records that were either added to or changed from one table to the next. Determining which is complicated by the fact that you may not have a primary key.
0
 

Author Comment

by:goodk
ID: 38374083
Wow, that works fine.  but this does not tell me that if in my table I have a duplicate record

I want to say that if two particular fields are repeated than the record is duplicated.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 25

Assisted Solution

by:lwadwell
lwadwell earned 1000 total points
ID: 38374583
Duplicate based on what?  All columns or only a subset?  Normally to determine that you would do something like:

SELECT col1, col2, col3
FROM table
GROUP BY col1, col2, col3
HAVING count(*) > 1
0
 
LVL 32

Accepted Solution

by:
awking00 earned 1000 total points
ID: 38376250
The original question was looking for records that had been added, removed, or changed from one table to the next. Now it seems you want to determine records that have certain fields that are the same. These are two different problems. Perhaps you can provide some sample data that include the various possibilities and what you want to see as results.
0
 

Author Closing Comment

by:goodk
ID: 38455681
super help; really appreciate it - thanks
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
Integration Management Part 2
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

834 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