把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