Link to home
Start Free TrialLog in
Avatar of Steynsk
SteynskFlag for Netherlands

asked on

Compare two SQL server tables with the exact same structure.

Hi Expert,

I've got two exact same tables (only there names differ).
Only there first field (employeeID) is unique.

And I'd like to extract three lists from them:

employee ID's that do exist in table1 and not in table2
employee ID's that do exist in table2 and not in table1
employee ID's that do exist in both tables but whom differ in from each other based on the other fields in the record

I've found out how to make the first two by:
SELECT * from table1 WHERE employeeID NOT IN (SELECT employeeID FROM table2)
SELECT * from table2 WHERE employeeID NOT IN (SELECT employeeID FROM table1)

But how do I show only the records employee ID's that are in both tables but where one or more of the other fields have chanced? And would it be possible to name the fields that differ in each record in these results?

CREATE TABLE table1](
	[employeeID] [char](5) NULL,
	[field1] [int] NULL,
	[field2] [nvarchar](max) NULL,
	[field3] [nvarchar](max) NULL,
	[field4] [nvarchar](max) NULL
) ON [PRIMARY]

CREATE TABLE table2](
	[employeeID] [char](5) NULL,
	[field1] [int] NULL,
	[field2] [nvarchar](max) NULL,
	[field3] [nvarchar](max) NULL,
	[field4] [nvarchar](max) NULL
) ON [PRIMARY]

Open in new window


I hope you can help,

Steynsk
ASKER CERTIFIED SOLUTION
Avatar of Éric Moreau
Éric Moreau
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Steynsk

ASKER

Excellent Thank you all for the useful answers.