SQL：按记录比较两个表记录 [英] SQL: compare two table record by record

问题描述

我想比较两个表记录。我有两个光标为每个表。代码如下

 声明游标c1 for SELECT * from Table1 
 OPEN c1 
 while @@ Fetch_status = 0 
 Begin 
为表2中的SELECT *声明游标c2 
 OPEN c2 
 WHILE @@ Fetch_Status = 0 
 Begin 
 / * * / 
 END 
 END 
  

当fecthing时，必须指定哪个光标

EDIT

对于每个记录在表1中我想要

• 根据主键在该记录中搜索Table2。


• 发现，我想根据table1中的列的值更新Table2中的额外列值。


• 当table2中缺少此记录时，我想将
  从table1复制到table2，并在
  table2中设置额外列的默认值。 li>
  

打开其他解决方案（不限于游标）

解决方案

如果表具有相同的列定义，fastes方式只是使用'except'子句：

 从表1 
除了
 SELECT * from Table2 
  

：

  SELECT * from Table2 
（
除外）SELECT * from Table1 
  

您会看到确切的设置差异：

EXCEPT和INTERSECT

I want to compare two tables recordby record. I have two cursor for each table. The code looks like this

Declare Cursor c1 for SELECT * from Table1
OPEN c1
While @@Fetch_status=0
Begin
  Declare Cursor c2 for SELECT * from Table2
  OPEN c2
  WHILE @@Fetch_Status=0
  Begin
     /*Comparison happens here*/
  END
END

When fecthing, must I specify which cursor I am fetching and how do I do that?

EDIT

For each record in Table1 I want to

• Search Table2 for that record based on the primary key.
• When it is found, I want to update the extra column values in Table2 based on the value of a column in table1.
• When this record is missing in table2, I want to copy it from table1 to table2 and set a default value of the extra column in table2.

Open to other solutions (not restricted to cursors)

解决方案

If tables have same column definition, the fastes way is just use 'except' clause:

SELECT * from Table1
except
SELECT * from Table2

also run it in opposite way:

SELECT * from Table2
except
SELECT * from Table1

You'll see the exact set difference:

EXCEPT and INTERSECT

这篇关于SQL：按记录比较两个表记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！