>>>I thought I knew this but I guess I don't
>>>
>>>I have two temp table #staging and #fc
>>>
>>>I want to insert rows in #fc from staging where they do not already exist based on a key
>>>
>>>Originally the key was event_id (int) so
>>>
>>>

>>>INSERT INTO #fc 
>>>(
>>>      Symbol
>>>      ,Eff_Date     
>>>...
>>>      ,FCA_Backfeed_Event_iD
>>>)
>>>
>>>SELECT 
>>>      DJ_SYMBOL
>>>      ,eff_date
>>>...
>>>    ,spi_event_id
>>>      FROM #staging
>>>      where #staging.spi_event_id  not in (select fca_backfeed_event_id  FROM  #fc)
>>>

>>>
>>>Worked great. Now I need to match on dj_symbol , eff_date not in (select symbol, eff_date from #fc )
>>>and it really doesn't like that.
>>>
>>>

>>>INSERT INTO #fc 
>>>(
>>>      Symbol
>>>      ,Eff_Date     
>>>...
>>>      ,FCA_Backfeed_Event_iD
>>>)
>>>
>>>SELECT 
>>>      DJ_SYMBOL
>>>      ,eff_date
>>>...
>>>    ,spi_event_id
>>>      FROM #staging
>>>      where #staging.DJ_symbol, #staging.eff_date  not in (select symbol, eff_date  FROM  #fc)
>>>

>>>
>>>symbols are char(6)
>>>eff_date is datetime
>>>
>>>Best I've been able to do is a kludgy cte at the top of Select symbol+convert(varchar, symbol, 104) as symdate
>>>and then the same on the #staging columns for my not in ( select symdate from cte ) at the bottom but it doesn't like that either.
>>>
>>>

>>>
>>>;WITH cte as ( select symbol+convert(varchar,eff_date,104) as symdate from #fc )
>>>INSERT INTO #fc 
>>>(
>>>      [Symbol]
>>>      ,[Eff_Date]      
>>>...
>>>      ,[FCA_Backfeed_Event_iD]
>>>      ,[SPISourceChecksum]
>>>)
>>>OUTPUT inserted.symbol, inserted.eff_date, inserted.fca_backfeed_event_id INTO @NewFCTemp
>>>SELECT 
>>>      [DJ_SYMBOL]
>>>      ,eff_date
>>>...
>>>      ,SPI_Event_Id
>>>
>>>      FROM #staging stg WHERE
>>>     stg.dj_symbol+convert(varchar,stg.eff_date,104) not in (select symdate from cte )
>>>      
>>>

>>>
>>>This works but I feel I am missing something more elegant and obvious.
>>>
>>>TIA
>>
>>Use
>>

>>WHERE NOT EXISTS (select 1 from #rfc R where R.eff_date = St.Eff_date and R.Event_ID = St.Even_ID etc.)

>>
>>

>>INSERT INTO #fc 
>>(
>>      Symbol
>>      ,Eff_Date     
>>...
>>      ,FCA_Backfeed_Event_iD
>>)
>>
>>SELECT 
>>      DJ_SYMBOL
>>      ,eff_date
>>...
>>    ,spi_event_id
>>      FROM #staging St
>>      where  not exists (select  1  FROM  #fc fc where fc.symbol = St.symbol and  fc.eff_date = St.eff_date)
>>

>
>Thanks once again Naomi. I suspected it had something to do with Exists but I really have never learned to do that properly. Maybe this time it will stick. <g>

It's also reputedly faster than IN.