Hi ,

I have two tables A and B, A has 8000 and B has 8122 records. I want to see what records are missing. I tried EXCEPT and it returned zero rows. I used where non exists also still no records. Can some one please advice me?

There are several things you an do.

SELECT t1.name
FROM table1 t1
LEFT JOIN table2 t2 ON t2.name = t1.name
WHERE t2.name IS NULL

or

SELECT name
FROM table2
WHERE name NOT IN
    (SELECT name 
     FROM table1)

or

SELECT name 
FROM table2 
WHERE NOT EXISTS 
    (SELECT * 
     FROM table2 
     WHERE table1.name = table2.name)

Here's another way. It does involve giving a full column list, though:

DECLARE @table1 TABLE (id INT)
INSERT INTO @table1 (id) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)

DECLARE @table2 TABLE (id INT)
INSERT INTO @table2 (id) VALUES (1),(2),(3),(4),(5),(6),(7),(8)

SELECT id, COUNT(*), MAX(tableName)
  FROM (
        SELECT id, 'table1' AS tableName
		  FROM @table1
		UNION ALL
		SELECT id, 'table2' AS tableName
		  FROM @table2
	   ) a
 GROUP BY ID
 HAVING COUNT(*) <> 2

SV,

Use simple sql query,

Select * from TableB where CommonColumn not in (select CommonColumn from TableA)

SELECT CommonColumn FROM TableB 
EXCEPT
SELECT CommonColumn FROM TableA ;

Do both tables have unique ID column? If yes, then EXCEPT should have returned the missing rows. If not, it means one of the tables have a few complete duplicates.