T-SQL - Insert a row in a table only if it doesn't already exist

Question

I have the T-SQL shown below. @Results is a table variable and 'CTE' is a common table expression. I only want to insert rows into the @Results table if the SubId I'm about to insert hasn't already been inserted into the table. The code shown below doesn't do the job 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)

Solution 1

You need to check for existance first:

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

Maybe you could put this requirement (only return those rows that don't already exist) into your CTE so that you won't have to filter the output from the CTE yet again...

Solution 2

I would do it like this (assumption - you don't have duplicate SubID in your CTE, that is you insert SubID of X and then later in this same query you insert the same one.)

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

Or you could roll the join I made into your CTE.

Solution 3

Try an `except clause:

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

Comments

[Hogan]

Isn't it better to use a join?

[marc_s]

@Hogan: no - why? What do you think you can gain from using a JOIN instead??

[Hogan]

because this check has to be done for each row (a new select) where a join is optimized in SQL to work quickly on sets of date. It is the difference between O(n) optimized vs O(1)+O(1)+...+O(1) n times. (See my answer for an example of using the join.)

[marc_s]

@Hogan: I would think the best approach would be to filter out those rows that already exist in the target table in the CTE and not return those at all.