Insert records from TABLE_A to TABLE_B without inserting any duplicates

Question

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

Answer 1

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