Insert records from TABLE_A to TABLE_B without inserting any duplicates-CodePudding

I have two tables

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 is where new data is created. CC_PERM is the main permanent data tables. All new data has to be placed here. There are no unique identifiers.

How do I insert the data from CC_TEMP into CC_PERM making sure there are no existing records vs from CC_PERM? I tried using a CTE without luck

CodePudding user response：

You can use a left join to make sure that the record doesn't already exist before inserting it.

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

If you sometimes have spaces or NULLs in some of the fields, then the following query can be used to convert NULL values to blanks.

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

CodePudding user response：

The normal way to approach this is using not exists

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]
);

Another way to approach this is using except which is useful if a column contains NULL values as it doesn't rely on equality but instead uses is 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