MERGE Vacancy.TB_FTSearchData as t -- target

USING vacancy.TB_Vacancy as s -- source

ON t.FTSearchVacancyInternalID = s.VacancyInternalID

AND t.FTSearchContractID = s.VacancyContractID

WHEN not Matched by source THEN DELETE

WHEN not Matched by target and s.VacancyFTSupdated = 1 and s.VacancyStatusID = 1 THEN

INSERT (

[FTSearchVacancyInternalID]

,[FTSearchVacancyTitle]

,[FTSearchVacancyCleanDescription]

,[FTSearchVacancyCategoryName]

,[FTSearchVacancyTagName]

,[FTSearchContractID]

,[FTSearchVacancyLCID]

,[CreatedDate]

,[ModifiedDate])

VALUES (

s.VacancyInternalID,

s.VacancyTitle,

s.VacancyCleanDescription,

dbo.fn_GetCSV(s.VacancyInternalID,1) ,

dbo.fn_GetCSV(s.VacancyInternalID,2) ,

s.VacancyContractID,

(Select la.LCID

from ReferenceData.TB_Language la

where la.languageInternalID = s.vacancyLanguageID),

GETDATE(),

NULL )

-- second statement not permitted, you could use the OUTPUT for this

/*

UPDATE

SET B.VacancyFTSupdated = 3

WHERE VacancyFTSupdated = 1

and VacancyStatusID = 1

and VacancyInternalID in (Select FTSearchVacancyInternalID from Vacancy.TB_FTSearchData)

*/

when matched and s.VacancyFTSupdated = 2 AND s.VacancyStatusID = 1 then

UPDATE

SET

FTSearchVacancyTitle= s.VacancyTitle

,FTSearchVacancyCleanDescription= s.VacancyCleanDescription

,FTSearchVacancyCategoryName= dbo.fn_GetCSV(s.VacancyInternalID,1)

,FTSearchVacancyTagName= dbo.fn_GetCSV(s.VacancyInternalID,2)

,[FTSearchContractID]= s.VacancyContractID

,[FTSearchVacancyLCID]= (

Select la.LCID

from ReferenceData.TB_Language la

where la.languageInternalID = s.vacancyLanguageID)

,[ModifiedDate]= GETDATE()

OUTPUT $action, Deleted.*, Inserted.* ;