T-SQL - insert a row into a table only if it does not already exist

Question

I have T-SQL shown below. @Results is a table variable, and CTE is a generic table expression. I only want to insert rows into the @Results table if the SubId that I am going to insert has not yet been inserted into the table. The code shown below does not do this work, and I'm not sure why. Can anyone see the problem?

Insert Into @Results (
    Cell, 
    CellSettings, 
    RecipeInstanceId, 
    BiasStartTime, 
    SubId 
    RuleInstanceId)
Select 
    Cell, 
    CellSettings, 
    RecipeInstanceId, 
    BiasStartTime, 
    SubId, 
    RuleInstanceId
From CTE
Where CTE.SubId NOT IN (Select SubId From @Results)

Answer 1

First you need to check for:

IF NOT EXISTS(SELECT * FROM @Results WHERE SubId = .......)
   INSERT INTO @Results (Cell, CellSettings, RecipeInstanceId, 
                          BiasStartTime, SubId, RuleInstanceId)
     SELECT 
         Cell, CellSettings, RecipeInstanceId, 
         BiasStartTime, SubId, RuleInstanceId
     FROM CTE

Perhaps you could put this requirement (only return those lines that do not yet exist) in your CTE so that you do not have to filter the output from the CTE again ...

Answer 2

( - SubID CTE, SubID X, .)

WITH CTE AS
( 
  blah
), CTENEW AS
(
   SELECT CTE.* 
   FROM CTE
   LEFT JOIN @Results R ON CTE.SubID = R.SubID
   WHERE R.SubID IS NULL
)
Insert Into @Results (
    Cell, 
    CellSettings, 
    RecipeInstanceId, 
    BiasStartTime, 
    SubId 
    RuleInstanceId)
Select 
    Cell, 
    CellSettings, 
    RecipeInstanceId, 
    BiasStartTime, 
    SubId, 
    RuleInstanceId
From CTENEW

, CTE.

Answer 3

Try the `except option:

insert MyTable(c1, c2, c3)

select ot.c1, ot.c2, ot.c3
from OtherTable ot

except

select mt.c1, mt.c2, mt.c3
from MyTable

Answer 4

Check if a record exists or not using "Exists"

If Not Exists(Select SubId From @Results)
    Insert Into @Results (
        Cell, 
        CellSettings, 
        RecipeInstanceId, 
        BiasStartTime, 
        SubId 
        RuleInstanceId)
    Select 
        Cell, 
        CellSettings, 
        RecipeInstanceId, 
        BiasStartTime, 
        SubId, 
        RuleInstanceId
    From CTE
    Where CTE.SubId NOT IN (Select SubId From @Results)