插入从表_a到table_b的记录，而无需插入任何重复项

Question

我有两个表

CC_TEMP 
   [Record Type] char(2)
  ,[Segment Code] char(2)
  ,[Headquarters Code] char(5)
  
CC_PERM
   [recID] numeric IDENTITY(1,1) NOT NULL
  ,[Record Type] char(2)
  ,[Segment Code] char(2)
  ,[Headquarters Code] char(5)

CC_TEMP是创建新数据的地方。 CC_PERM是主要的永久数据表。所有新数据都必须在此处放置。没有唯一的标识符。

如何将数据从CC_TEMP插入CC_PERM中，以确保没有CC_Perm的现有记录与CC_Perm？我尝试使用不运气的CTE

Answer 1

您可以使用左联接来确保在插入记录之前尚不存在。

INSERT INTO CC_PERM
SELECT T.*
FROM CC_TEMP T
LEFT JOIN CC_PERM P ON
    T.[Record Type] = P.[Record Type]
    AND T.[Segment Code] = P.[Segment Code]
    AND T.[Headquarters Code] = P.[Headquarters Code]
WHERE P.[recID] IS NULL

如果您有时在某些字段中有空格或空，则可以使用以下查询将空值转换为空白。

INSERT INTO CC_PERM
SELECT T.*
FROM CC_TEMP T
LEFT JOIN CC_PERM P ON
    ISNULL(T.[Record Type], '') = ISNULL(P.[Record Type], '')
    AND ISNULL(T.[Segment Code], '') = ISNULL(P.[Segment Code], '')
    AND ISNULL(T.[Headquarters Code], '') = ISNULL(P.[Headquarters Code], '')
WHERE P.[recID] IS NULL

Answer 2

使用的正常方法是不存在

insert into CC_PERM ([Record Type], [Segment Code], [Headquarters Code])
select [Record Type], [Segment Code], [Headquarters Code]
from CC_TEMP t
where not exists (
  select * from CC_PERM p where 
      p.[Record Type]       = t.[Record Type] 
  and p.[Segment Code]      = t.[Segment Code] 
  and p.[Headquarters Code] = t.[Headquarters Code]
);

另一种方法是使用 ，如果列包含null值，则很有用，因为它不依赖于平等，而是而是使用是null

insert into CC_PERM ([Record Type], [Segment Code], [Headquarters Code])
select [Record Type], [Segment Code], [Headquarters Code]
from CC_TEMP
except
select [Record Type], [Segment Code], [Headquarters Code]
from CC_PERM