[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 #A
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME ='CUST1' AND TABLE_CATALOG='dbname'

SELECT distinct     	 TABLE_NAME,COLUMN_NAME INTO #B
FROM INFORMATION_SCHEMA.COLUMNS
WHERE 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 CUST1
UNION 
SELECT ID,DT FROM CUST2