sql - How to get the records back from two tables with same primary key if any of the other column fields are changed -

my previous question confusing. sorry carelessness. here, posted question again more information.

my table , table b has same column names(name,id,age,date,class,...) different number of rows. table b duplicate table of table , has fewer rows. want know how can retrieve records if have same primary key(id) , of other column fields (name, age, date, class,...) different. however, there 1 condition. although records have same primary key, if date changed, records should not retrieved.only when 2 tables have same primary key, date different , of column fields changed, records should retrieved.

since there around 200k records, , around 100 columns, use advanced sql, since sql long if use select.. from... where, don't know sql use.

tablea:

name    age  id  date ------  ---  --  ---------- david   11   1   11/01/2014 claire  16   2   13/03/2014 max     15   3   20/02/2014 john    14   4   19/09/2014 james   12   5   16/06/2014 

tableb:

name   age  id  date -----  ---  --  ---------- max    15   3   15/05/2014   14   4   12/04/2014 bill   12   7   11/04/2014 paul   11   8   24/12/2013 kevin  13   9   03/04/2014 

output expected:

tablea                       tableb  name  age  id  date          name  age  id  date ----  ---  --  ----------    ----  ---  --  ---------- john  14   4   19/09/2014     14   4   12/04/2014 

thanks!

since want check columns writing clause might tedious can use information_schema.columns column names table , using dynamic query can check column differences.

the following might solution problem.

--simulate table structure create table tablea (     name varchar(100),     age int,     id int,     date_col datetime )  create table tableb (     name varchar(100),     age int,     id int,     date_col datetime )  --data testing insert tablea(name, age, id, date_col) values('david',11,1,'01/11/2014') insert tablea(name, age, id, date_col) values('claire',16,2,'03/13/2014') insert tablea(name, age, id, date_col) values('max',15,3,'02/20/2014') insert tablea(name, age, id, date_col) values('john',14,4,'09/19/2014') insert tablea(name, age, id, date_col) values('james',12,5,'06/16/2014')  insert tableb(name, age, id, date_col) values('max',15,3,'05/15/2014') insert tableb(name, age, id, date_col) values('will',14,4,'04/12/2014') insert tableb(name, age, id, date_col) values('bill',12,7,'04/11/2014') insert tableb(name, age, id, date_col) values('paul',11,8,'12/24/2013') insert tableb(name, age, id, date_col) values('kevin',13,9,'04/03/2014')   --solution starts here  create table #tablecols (     id int identity(1,1),     column_name varchar(1000) )   --since both tables have same columns can take columns of 1 table insert #tablecols (column_name) select column_name information_schema.columns table_name = 'tablea';  declare @startcount int, @maxcount int, @col_name varchar(1000), @query varchar(8000), @subquery varchar(8000)  select @startcount = 1, @maxcount = max(id) #tablecols; select @query = '', @subquery = ''  while(@startcount <= @maxcount) begin     select @col_name = column_name #tablecols id = @startcount;      if(@col_name != 'date_col' , @col_name != 'id')     begin         set @subquery = @subquery + ' a.' + @col_name + ' != b.' + @col_name + ' or ';     end      set @startcount = @startcount + 1 end  set @subquery = left(@subquery, len(@subquery) - 3); set @query = 'select a.*, b.* tablea inner join tableb b on a.id = b.id a.date_col != b.date_col , (' + @subquery + ')'; exec (@query); 

hope helps.