MS SQL不存在更多列

Question

我有表1，我需要从表1中拿走，而table 2仅在表2中不在表2

中

Table 1
ID  Name    N   T
A   Bob    33   Y
B   Eva    44   N
C   John   47   Y
B   Alex   44  N
D   Bob    47  Y
Table 2 
ID  Name   N   T
A   Bob    34  Y
B   Alex   44  N
D   Bob    47  Y

我尝试设置此类型的版本脚本，但我获得了整个表1 新行的重复性

INSERT INTO TABLE_2
  (ID , Name ,  N ,  T)
SELECT table1.ID,  table1.Name,   table1.N,   table1.T
FROM TABLE_1 table1 
WHERE NOT EXISTS(SELECT 1
                 FROM TABLE_2 table2
                 WHERE (table2.ID = table1.ID) and (table2.Name = table1.Name) and (table2.N = table1.N )AND (table2.T =  table1.T))

期望结果

A   Bob    34   Y
B   Alex   44   N
D   Bob    47   Y
A   Bob    33   Y
B   Eva    44   N
C   John   47   Y

Answer 1

一种方法将是使用SELECT查询的INSERT INTO ... SELECT，识别table1中的所有记录，这些记录与table2中的某些记录不完全匹配。

INSERT INTO table2 (ID, Name, N, T)
SELECT t1.ID, t1.Name, t1.N, t1.T
FROM table1 t1
LEFT JOIN table2 t2
    ON t1.ID   = t2.ID   AND
       t1.Name = t2.Name AND
       t1.N    = t2.N    AND
       t1.T    = t2.T
WHERE t2.ID IS NULL

请注意，这里的逻辑是，如果table1中的给定记录的一列也不一致，则与table2中的某些记录一致，则JOIN将失败，并且所有table2列将在结果集中出现NULL。WHERE子句仅限于table1中的此类非匹配记录。

Answer 2

您可以将两个表与左联接加入。

必须插入没有与表_2匹配的表_1的所有记录。要检查哪个记录没有匹配项，您可以简单地使用T2.ID IS NULL

 INSERT INTO TABLE_2 (ID , Name ,  N ,  T)
      SELECT T1.ID
            ,T1.Name
            ,T1.N
            ,T1.T
        FROM TABLE_1 T1 
   LEFT JOIN TABLE_2 T2
          ON T1.ID    = T2.ID
         AND T1.Name  = T2.Name
         AND T1.N     = T2.N
         AND T1.T     = T2.T
       WHERE T2.ID IS NULL

Answer 3

declare @Table1 table (ID varchar(100),  Name varchar(100),   N int,   T varchar(100));
insert into @Table1 values
('A',   'Bob',    33,   'Y'),
('B',   'Eva',    44,   'N'),
('C',   'John',   47,   'Y'),
('B',   'Alex',   44,  'N'),
('D',   'Bob',    47,  'Y')
declare @Table2 table (ID varchar(100),  Name varchar(100),   N int,   T varchar(100));
insert into @Table2 values
('A',   'Bob',    34,   'Y'),
('B',   'Alex',   44,  'N'),
('D',   'Bob',    47,  'Y')
insert into @Table2
select *
from @Table1 t1
where not exists (select * 
                  from @Table2 t2
                  where t2.ID = t1.ID and t2.N= t1.N and t2.Name = t1.Name and t1.T = t2.T);