SQLTeam.com | Weblogs | Forums

Comparing tables


Is there a way to quickly check the records delta between two tables for the fields that have the same name in these tables? Similar to Left/Right Anti join but I would also need to see where the delta is exactly, i.e. if the table has 5 fields and one of them results in a discrepancy, I will need to see that easily rather than be shown the whole record.

Also, can I use SQL (Azure Data Studio) to compare one table from the server/cloud and one table from my local laptop even though I have no access to create temp tables?


"Quickly"? Depends on the size of the table. In order to know which specific columns changed, you will have to compare every column.

Quickly I mean with 1-2 lines of code, is there a diff command or something?

I'd generate the code using sys.columns. It likely won't be just 1 or 2 lines, but it can be generated in a couple of seconds.

how are you planning on comparing and differentiating one row from another and avoid cartesian?
or is there a correlation between one row in table A and Table B, a column you will use to join them with?

create table #mish(id int, name varchar(50))

insert into #mish
values(1, 'Vader')
create table #mash(id int, name varchar(50))
insert into #mash
values(1, 'Vaader')

select *
  from #mish i
  join #mash v on i.id = v.id
  where i.name != v.name

  select *
  from #mish i
  cross apply #mash v
  where i.name != v.name

drop table #mish
drop table #mash