Steynsk
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:
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?
I hope you can help,
Steynsk
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]
I hope you can help,
Steynsk
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER