Compare two tables and give the output record which does not exist in 2nd table

Question

0.00/5 (No votes)

See more:

i am comparing 2 tables,1 table is view table(from other server) and the other 1 is my database table.The output will show the record which doesnt exist in 2nd table.

use INTRANET

SELECT *
FROM HRS_EMPLOYEEMASTER A
WHERE NOT EXISTS (
SELECT 100000
FROM UserInfo B
WHERE A.EmployeeNo = B.EmployeeNo
);

But why am i getting this error?

C#

Msg 468, Level 16, State 9, Line 8
Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

What I have tried:

have no idea ,please guide me for this issue,thank you.

Posted 30-Jun-16 15:22pm

KyLim0211

Updated 2-Jul-16 5:14am

Add a Solution

Comments

[no name] 2-Jul-16 10:52am

I would suggest (Performance wise) to do it like this:
SELECT DISTINCT A.EmployeeNo
FROM HRS_EMPLOYEEMASTER A
LEFT JOIN UserInfo B ON B.EmployeeNo = A.EmployeeNo
WHERE B.EmployeeNo IS NULL

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

/\jmot · Answer 1 · 2016-07-02T05:14:00

Solution 2

You can try something like this.

C#

SELECT A.*
FROM HRS_EMPLOYEEMASTER A
WHERE A.EmployeeNo  Not In (
SELECT B.EmployeeNo 
FROM UserInfo B
);

Posted 2-Jul-16 5:14am

/\jmot

Compare two tables and give the output record which does not exist in 2nd table

1 solution

Solution 2

Add your solution here

Preview 0

Compare two tables and give the output record which does not exist in 2nd table

1 solution

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us