UNION ALL will cause each Name to be repeated if it appears in both tables.
Are the ID values significant? If by "display the Common" you mean you only want a single row with each Name value you might use this query:SELECT Name FROM #temp1
UNION
SELECT Name FROM #temp2
If you want to know which table the data came from and the id, but still want to avoid duplicates, use this (assuming the names are unique within each table):SELECT id = ISNULL(t1.id, t2.id),
Name = ISNULL(t1.Name, t2.Name),
SourceTable = CASE WHEN t1.id IS NULL THEN 'temp2' ELSE 'temp1' END
FROM #temp1 t1
FULL JOIN #temp2 t2 ON t1.Name = t2.Name
If you want to suppress duplicates within the tables as well as between them:SELECT id = ISNULL(t1.id, t2.id),
Name = ISNULL(t1.Name, t2.Name),
SourceTable = CASE WHEN t1.id IS NULL THEN 'temp2' ELSE 'temp1' END
FROM ( SELECT id=MIN(id), Name FROM #temp1 GROUP BY Name) t1
FULL JOIN ( SELECT id=MIN(id), Name FROM #temp2 GROUP BY Name) t2 ON t1.Name = t2.Name