Link to home
Start Free TrialLog in
Avatar of tselectro
tselectro

asked on

Display table difference

I have to identical tables:
Table1: TagID (Primary key), PlantName,[Object],[Type]
Table2: TagID (Primary key), PlantName,[Object],[Type]

I want to list rows that exist in Table2, but have different data

Ex
Table1
TagID (PK)      PlantName      Object      Type
KRA.Tag1            KP100            Pump1      A
KRA.Tag2            KP100            Pump2      B
KRA.Tag3            KP101            Motor1      B

Table2
TagID (PK)      PlantName      Object      Type
KRA.Tag1            KP100            Pump1      A
KRA.Tag2            KP100            Pump2      C
KRA.Tag3            KP101            Motor2      C
KRA.Tag4            KP102            Motor1      A (exclude this row, does not exist in Table1)

Result I want:
TableName      TagID (PK)      PlantName      Object            Type
Table1            KRA.Tag2            KP100            Pump1            B
Table2            KRA.Tag2            KP100            Pump1            C (diff)
Table1            KRA.Tag3            KP101            Motor1            B
Table2            KRA.Tag3            KP101            Motor2 (diff)      C (diff)


Someone who can help me with the ms sql syntax?
Avatar of Ioannis Paraskevopoulos
Ioannis Paraskevopoulos
Flag of Greece image

SELECT * 
FROM Table1 T1 
JOIN Table2 T2 
ON T1.TagID = T2.TagID
WHERE T1.PlantName <> T2.PlantName 
OR T1.Object <> T2.Object
OR T1.Type <> T2.Type

Open in new window


Giannis
"I want to list rows that exist in Table2, but have different data "

I would use new SQL functions EXCEPT or INTERSECT for that matter and just keep in mind if you have text or ntext data columns you will need to convert them to varchar(max) or nvarchar(max).

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

so if the columns are the same a simple:

select * from table2
except
select * from table1

should do it.
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America 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