SQL Server compares two-table columns _mssql

I. Problems
Gives two tables with forty or fifty columns and finds the same columns and different columns

Second, query two table columns, there is a temporary table

--#a, #b都是临时表, automatically deleted after the current connection is disconnected
--rank () over (ordered by Syscolumns.name DESC) as is supported by SQL2005, plus a self increment number before each line record
--identity (int,1,1) function must be used in conjunction with into

1, the table column into the #a--' desttbl ' comparison table name

SELECT * Into #a from (select RANK () syscolumns.name DESC) as ordinal number, syscolumns.name
From Syscolumns,sysobjects
where syscolumns. [Id]=sysobjects. [ID]
and sysobjects. [name]= ' desttbl ') as T

SELECT * FROM #a

1 Name
2 Course
3 ID
4 CNO

2, the table column into the #b--' student ' comparison table name

Select serial Number = IDENTITY (int,1,1), Syscolumns.name
Into #b from syscolumns,sysobjects
where syscolumns. [Id]=sysobjects. [ID]
and sysobjects. [name]= ' Student '

SELECT * FROM #b

1 ID
2 Name
3 CNO

Third, analyze and compare the similarities and differences of various table columns

Use the following statement, or slightly change the comparison
SELECT * from #b where name in (select name from #a)
SELECT * from #a where name isn't in (select name from #b)
SELECT * from #a A, #b b where a.name=b.name
SELECT * FROM #a a LEFT join #b B on a.name=b.name