Hi SSIS Guru,
Our existing process pulls data from source (Internet) into our staging database nightly. We then have a big stored procedure to do all kind of insert, update and delete records into destination table. There are two big logic inside this stored procedure:
The stored procedure inserts data into temporary table (called #temp).
1. For existing records, update to relavant tables.
Update sales.customer set active=1 From sales.customer a join #temp b a.customerID=b.customerID where a.active !=1
Update sales.customer set active=0 From sales.customer a join #temp b a.customerID=b.customerID where a.active !=0
INSERT into sales.customer (customerID,name,address)
select a.customerid,a.name,a.address from #temp a join customer_main b on a.customerID=b.customerID
AND NOT EXISTS (select 'something' from sales.customer c where a.customerID=b.customerID)
We have five more tables that doing the dame update logic.
2. For new records, insert to relevant tables.
Use CURSOR doing insert. Below is logic of inserting new customer:
declare c cursor forward_only for select customerID from #temp a
where NOT EXISTS(select 'something' from sales.Customer_Load b where
View Complete Post