Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Insert where compound key not in ( select ... )
Message
From
11/07/2012 16:09:48
 
 
To
All
General information
Forum:
Microsoft SQL Server
Category:
SQL syntax
Title:
Insert where compound key not in ( select ... )
Miscellaneous
Thread ID:
01548230
Message ID:
01548230
Views:
63
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


Charles Hankey

Though a good deal is too strange to be believed, nothing is too strange to have happened.
- Thomas Hardy

Half the harm that is done in this world is due to people who want to feel important. They don't mean to do harm-- but the harm does not interest them. Or they do not see it, or they justify it because they are absorbed in the endless struggle to think well of themselves.

-- T. S. Eliot
Democracy is two wolves and a sheep voting on what to have for lunch.
Liberty is a well-armed sheep contesting the vote.
- Ben Franklin

Pardon him, Theodotus. He is a barbarian, and thinks that the customs of his tribe and island are the laws of nature.
Next
Reply
Map
View

Click here to load this message in the networking platform