Daniel,
Use the Binary_CheckSum function. It can aggregate a value for all the columns you are comparing, it works pretty fast too. I had to put together a process that compared multimillion row tables to find the rows that needed updating.
select Main.PK
from Main
inner join Secondary
on Main.PK = Secondary.PK
where binary_checksum( Main.ColA, Main.ColB, Main.ColC ... ) !=
binary_checksum( Secondary.ColA, Secondary.ColB, Secondary.ColC ... )
You can use left joins or exists checks to find the rows that need to be inserted.
>I already have this done in Foxpro, scaning and comparing data in 2 tables, but is there a way
>to do this in SQL Server using SQL???
>Basically I have 2 tables that are identical in structure, but the data in one table may not be the same as the other, so I want to find out where the discrepancies are. Is this possible?