I wanna find the difference between two tables. Table A has 6 columns and 5 of the columns are part of the primary key. Table B has 5 columns and has no primary key defined on it. All the datatype and columns match between the two tables. I wanne find only the difference data in Table A . Which is the best query to do this.
You can write a query joining both the tables using LEFT JOIN on the columns which you want to join. Then in the where clause add the condition to filter out NON null values and process only rows where table b has got null values. Same operation you can perform by flipping the tables. In short a full join but considering only the NULL values
SQL Server 2005 Books Online (September 2007) tablediff Utility http://msdn2.microsoft.com/en-us/library/ms162843.aspx