极具挑战的有关问题-sql合并两张表为一张表

极具挑战的问题----sql合并两张表为一张表
问题陈述：
  把两张表头尾相接合并起来，已知的条件是：两张表的表名是知道的，列名和列的数目都不知道（列的数量可以相同，也可以不相同），两张表的列的名称有相同的，也有不相同的。
  合并的要求是这样：两张表相同列名的只保留一列，列名不相同的保留下来。
举个简单例子：

有两表，表1和表2

SQL code

表1[a]       [b]        [c]www       123        69.09fff       998        69.08uuu       388        69.06表2[e]       [d]        [c]        [f]ttt       ddd        89.12      u78usa       jio        83.21      pok结果:表3[a]       [b]        [c]        [e]            [d]            [f]www       123        69.09                                fff       998        69.08      uuu       388        69.06                     89.12        ttt            ddd            u78                     83.21        usa            jio            pok

SQL code

declare @t table(a varchar(20),b varchar(20) ,c varchar(20))insert into @t select 'www'     ,  '123'   ,     '69.09' union allselect 'fff',       '998',        '69.08' union allselect 'uuu',       '388',        '69.06'declare @t1 table(e varchar(20),d varchar(20),c varchar(20),f varchar(20))insert into @t1 select 'ttt'  ,     'ddd  '   ,   '89.12'   ,   'u78' union all select 'usa',       'jio',        '83.21',      'pok'select isnull(a,'') a,isnull(b,'') b,c=ISNULL(t.c,t1.c),isnull(e,'') e,isnull(d,'') d ,isnull(f,'') f from @t t full join @t1 t1 on t.c=t1.c SELECT A,B,C,'' e,'' d,'' f FROM @t UNION ALL SELECT '','',C,E,D,F FROM @t1/*a                    b                    c                    e                    d                    f-------------------- -------------------- -------------------- -------------------- -------------------- --------------------www                  123                  69.09                                                          fff                  998                  69.08                                                          uuu                  388                  69.06                                                                                                    89.12                ttt                  ddd                  u78                                          83.21                usa                  jio                  pok(5 行受影响)*/
[解决办法]
SQL code
---测试数据--- 
 if object_id('[t1]') is not null drop table [t1] 
 go 
 create table [t1]([a] varchar(3),[b] int,[c] numeric(4,2)) 
 insert [t1] 
 select 'www',123,69.09 union all 
 select 'fff',998,69.08 union all 
 select 'uuu',388,69.06 
 if object_id('[t2]') is not null drop table [t2] 
 go 
 create table [t2]([e] varchar(3),[d] varchar(3),[c] numeric(4,2),[f] varchar(3)) 
 insert [t2] 
 select 'ttt','ddd',89.12,'u78' union all 
 select 'usa','jio',83.21,'pok' 
   
 -->查询 
  
 declare @sql1 varchar(8000),@sql2 varchar(8000) 
  
 select @sql1=isnull(@sql1+',','')+name  from syscolumns where id=object_id('t1')  
 select @sql1=@sql1+','''' as '+name from syscolumns where id=object_id('t2') and name not in(select name from syscolumns where  id=object_id('t1')) 
  
 select @sql2=isnull(@sql2+',','')+''''' as '+name from syscolumns where id=object_id('t1') and name not in(select name from syscolumns where  id=object_id('t2')) 
 select @sql2=@sql2+','+name  from syscolumns where id=object_id('t2')  
  
 exec ('select '+@sql1+' from t1 union all select '+@sql2+' from t2')  
 
  
 /** 
 a  b       c    d  e  f   
 ---- ----------- ------ ---- ---- ----  
 www  123     69.09       
 fff  998     69.08       
 uuu  388     69.06       
    0       89.12  ddd  ttt  u78 
    0       83.21  jio  usa  pok 
 **/