How would I go about getting everything from table A and everything from table B that is not in table A? I'm trying to prevent duplicate information coming from table B when combining the two tables.
SELECT Col1, Col2, Col3 FROM TableA UNION SELECT Col1, Col2, Col3 FROM TableB
There is one caveat though. If there were two (or more) rows with the same values of Col1, Col2, Col3 in TableA this query would return only one of those. In other words, it de-duplicates the combination of all the rows in TableA and TableB taken together.
If you want to know which table it came from? then perhaps
SELECT 'A' AS Source, Col1, Col2, Col3 FROM TableA AS A UNION ALL SELECT 'B' AS Source, Col1, Col2, Col3 FROM TableB AS B WHERE NOT EXISTS ( SELECT * FROM TableA AS A WHERE A.KeyColumn1 = B.KeyColumn1 AND A.KeyColumn2 = B.KeyColumn2 ... )
I expect you could also do a FULL OUTER JOIN but I tend to find that the syntax of that get's in my way and I wind up reverting to EXISTS / NOT EXISTS instead.
Like James said, his code will easily do the trick with the caveat that any duplicates in TableA will also be removed.
If you really do need ALL rows from TableA (even the duplicates), then this will do the trick.
--===== Create some test data. -- This is NOT a part of the solution. SELECT * INTO #TableA FROM ( SELECT 1,2 UNION ALL SELECT 3,4 UNION ALL SELECT 1,2 UNION ALL --Dupe in A (display the dupe) SELECT 5,6 UNION ALL SELECT 7,8 ) d (Col1,Col2) ; SELECT * INTO #TableB FROM ( SELECT 1,2 UNION ALL --Dupe in A (Don't display the dupe) SELECT 3,4 UNION ALL --Dupe in A (Don't display the dupe) SELECT 6,5 UNION ALL SELECT 8,7 ) d (Col1,Col2) ; --===== Different type of solution. -- Also equates NULLs but not demonstrated here. SELECT * FROM #TableA UNION ALL SELECT * FROM ( SELECT * FROM #Tableb EXCEPT SELECT * FROM #TableA ) d ;