comparing two tables and exporting the differences in sql server

Question

Post reply

comparing two tables and exporting the differences in sql server

jeremydwilliams

SSC Rookie

Points: 29
More actions
April 19, 2010 at 8:45 pm

#233999

Hi all !, i am trying to compare four columns in two tables(both are in the same schema), table a has two columns for firstname and last name , table b has similar columns firstname and last name, i wish to compare both and if there are similarities, do nothing but if there are differencies, export or write them to a exception or error table.. not quite sure what if statement i should write or how to go about this ...any help would be appreciated.

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323753 More actions · Answer 1

jeremy you'll want to left outer join the two tables;

by checking a third column in the table, you can find which items in tableb do not exist in tablea

this example is joining on two columns,,,just keep adding till you have the four you wanted to test

select * from tablea

left outer join tableb

on tablea.firstname = tableb.firstname

and tablea.lastname = tableb.lastname

where tableb.id is null

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

jeremydwilliams SSC Rookie Points: 29 More actions · Answer 2

jeremydwilliams

SSC Rookie

Points: 29

April 19, 2010 at 11:42 pm

#1154318

Thanks Lowell , will certainly try this!

WayneS SSC Guru Points: 95461 More actions · Answer 3

jeremy williams-435563 (4/19/2010)
Hi all !, i am trying to compare four columns in two tables(both are in the same schema), table a has two columns for firstname and last name , table b has similar columns firstname and last name, i wish to compare both and if there are similarities, do nothing but if there are differencies, export or write them to a exception or error table.. not quite sure what if statement i should write or how to go about this ...any help would be appreciated.

You should also look into the EXCEPT operator:

insert into ExceptionTable (col1, col2, col3, col4)

select col1, col2, col3, col4 from table1

EXCEPT

select col1, col2, col3, col4 from table2

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Paul White SSC Guru Points: 150468 More actions · Answer 4

While we are mentioning alternatives, not everyone knows about the useful tablediff utility shipped with SQL Server:

tablediff Utility

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

jeremydwilliams SSC Rookie Points: 29 More actions · Answer 5

Interesting, Paul,i did not know about this tool either, thanks for sharing..

jeremydwilliams SSC Rookie Points: 29 More actions · Answer 6

Wayne appreciate the input !, thanks guys , really appreciate your suggestions, I tried this bit of code and I post it here so that others can be helped, again thanks guys really appreciate this

SELECT CASE WHEN t1.FirstName<>t2.FirstName

THEN

'ERROR'

ELSE

t1.FirstName

END as FirstName,

CASE WHEN t1.LastName<>t2.LasttName

THEN

'ERROR'

ELSE

t1.LastName

END as LastName

FROM

Table1 t1 INNER JOIN Table2 t2

ON t1.tid=t2.tid

Paul White SSC Guru Points: 150468 More actions · Answer 7

Jeremy,

One quick question: are either or both of the FirstName and LastName columns NULLable?

If so, you would have to add some extra logic to correctly handle all possible combinations. Just something to be aware of.

Paul

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

jeremydwilliams SSC Rookie Points: 29 More actions · Answer 8

Paul,

Thanks for your response , you are right, both of these columns are nullable in fact , they were already created when i got them or i would have changed them otherwise also there exist duplicates , could you tell me why the following code script is not working?

SELECT DISTINCT [First Name], [last Name] from table1

COUNT[First Name] AS NumOccurrences and COUNT[Last Name] AS NumOccurences

GROUP BY [First Name] and [last Name]

HAVING ( COUNT[First Name] > 1 )

and HAVING ( COUNT[Last Name] > 1 )

really appreciate this..

Paul White SSC Guru Points: 150468 More actions · Answer 9

I think you probably meant to write something like this:

SELECT [First Name],

[Last Name],

COUNT([First Name]) AS FirstNameCount,

COUNT([Last Name]) AS LastNameCount

FROM Table1

GROUP BY

[First Name],

[Last Name]

HAVING COUNT([First Name]) > 1

OR COUNT([Last Name]) > 1;

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

jeremydwilliams SSC Rookie Points: 29 More actions · Answer 10

Paul, you are so correct!!, thanks a million!,this worked!,a next question if you do not mind, is it possible to have a script that targets these duplicates and removes them? just asking!, trying hard to learn tsql but so many things and so litte time! 🙂

Thanks for all your help, sorry for asking two questions in one post maybe i should have started a new thread.Sorry moderators!

jeremydwilliams SSC Rookie Points: 29 More actions · Answer 11

Paul, thanks for your help so far, i however have an additional question,

I have 2 tables. I wanted to find the rows in one table that doesn't exist in another table. However, the column (column1) I wanted to find if it doesn't exist in the other table should be concatenated with 2 columns (column2 & column3). Also, column1 is a haystack instead of a needle so, I can not say column1 like '%'+column2+' '+column3+'%' because, "dean clarke something" is not like "%"+"dean clarke"+"%". Below is my code snippet and any help will be kindly appreciated.

SELECT [User Name]

FROM dbo.table1

WHERE (NOT EXISTS

SELECT '%' + dbo.table2.[First Name] + ' ' + dbo.table2.[Last Name] + '%'

FROM dbo.table1 CROSS JOIN

dbo.table2

WHERE (table1.[User Name] LIKE '%' + dbo.table2.[First Name] + '%') AND

(table1.[User Name] LIKE '%' + dbo.table2.[Last Name] + '%')