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.