I have a request to modify / insert new data in the products based on Value.
Currently I'm using SSIS to insert data into heap table and then I need to move records to table based on Status.
I have 3 status Add, Update, Remove.
How I can write a insert / update query based on mentioned below statuses?
Here is logic:
Wrap 1 or 2 up in a stored procedure and then simply call that procedure from SSIS. Personally, I'm using a version of example 1 where the insert/update code are individually wrapped procedures that the upsert procedure simply calls in the proper order. This is slightly less efficient than example 2 but I have found it to be a more flexible and maintainable pattern (especially when paired with table-valued parameters).
create procedure dbo.usp_MyTable_Upsert as begin; update <Target> set ... from <Source> join <Target> on ...; insert into <Target>(...) select ... from <Source> where not exists ( select 1 from <Target> where ... ); end;
create procedure dbo.usp_MyTable_Upsert as begin; merge <Target> using <Source> on ... when matched then update set ... when not matched by target then insert ... when not matched by source then delete; end;