Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to compare the columns of the two tables in sqlserver. Many people may not know much about it. In order to make you understand better, the editor has summarized the following contents for you. I hope you can get something according to this article.

First, the question is given to two tables with 40 or 50 columns each, and find out the same columns and different columns.

Second, query the columns of two tables, there are temporary tables

-- # a, # b are temporary tables that are automatically deleted after the current connection is disconnected-- RANK () OVER (ORDER BY syscolumns.name DESC) AS is supported by SQL2005 and preceded by a self-incrementing serial number-- the IDENTITY (INT,1,1) function must be used in conjunction with into.

1. Put the column of the table into the name of the table compared by # a Murray

Select * into # a from (select RANK () OVER (ORDER BY syscolumns.name DESC) AS serial 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 id4 cno

2. Put the column of the table into the name of the table compared by # b Mustang

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 id2 name3 cno

Third, analyze and compare the similarities and differences of each table.

Compare select * from # b where name in (select name from # a) select * from # a where name not in (select name from # b) select * from # a, # b b where a.name=b.name select * from # an a left join # b b on a.name=b.name with the following statements or minor changes

After reading the above, do you have any further understanding of how to compare the columns of the two tables in sqlserver? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.