Match what does not match - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Match what does not match

Author

Topic

jfm
Posting Yak Master

145 Posts

Posted - 2012-09-17 : 05:55:15

Hi guys,

How you cross two tables, and match into a new_table the info that doesn't match between both of them?

Both tables have the same columns,

Thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-17 : 06:25:07

Do you want to match on ALL columns, or just the primary keys? I am assuming all columns in the query below.

Also "CROSS" has a special meaning in SQL - equivalent to a Cartesian product. I am assuming that is not what you meant. If my assumption is correct:

SELECT * INTO TableNew
FROM 
(
SELECT * FROM TableA EXCEPT SELECT * FROM TableB
UNION ALL
SELECT * FROM TableB EXCEPT SELECT * FROM TableA
) s

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-17 : 10:57:08

[code]
INSERT NewTable
SELECT COALESCE(a.PK,b.PK),
COALESCE(a.col1,b.col1),
COALESCE(a.col2,b.col2),
...
FROM TableA a
FULL JOIN TableB b
ON b.PK = a.PK
WHERE b.PK IS NULL
OR a.PK IS NULL
[/code]

if new_table doesnt exist already use SELECT...INTO

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources