[MSSQL] 找出2个table的相同栏位,把资料union起来

把2个table的资料union起来,但2个table很相似,却有些栏位差异.
怎么快速的找出共同栏位(来做union)呢?

环璄:SQL SERVER 2008

先建立2个table,有共同栏位,也各有一个不同的栏位:UDT/UTS

CREATE TABLE CUST1(ID INT,DT INT,UPD INT--这个不同.)CREATE TABLE CUST2(ID INT,DT INT,UTS INT--这个不同.)

取出栏位,等等做比较

SELECT distinct    TABLE_NAME,COLUMN_NAME INTO #AFROM INFORMATION_SCHEMA.COLUMNSWHERE TABLE_NAME ='CUST1' AND TABLE_CATALOG='dbname'SELECT distinct      TABLE_NAME,COLUMN_NAME INTO #BFROM INFORMATION_SCHEMA.COLUMNSWHERE TABLE_NAME ='CUST2' AND TABLE_CATALOG='dbname'

比较异同,可以清楚看出,哪些栏位A有,B没有.

SELECT * FROM #A A FULL OUTER   JOIN #B B ON A.COLUMN_NAME = B.COLUMN_NAME  

取出相同的,来select 做union
这样union,不用怕栏位数不同,也不用怕栏位不一样.

DECLARE @RET VARCHAR(MAX)=''SELECT @RET = A.COLUMN_NAME+','+@RET  FROM #A A INNER JOIN  #B B ON A.COLUMN_NAME = B.COLUMN_NAME  SELECT @RET --result--ID,DT,

SELECT ID,DT FROM CUST1UNION SELECT ID,DT FROM CUST2