Improve Insert performance for 200 Million rows
So many things...

If you have the Actual Execution Plan, please post it. I'm curious to see what indexes you have, and what the shape of the data is.

In the meantime, be aware that:

ON eord.lifnr = 'IVG'+ zsm2.werks


...isn't great for finding rows in zsm2. You may want to add:

AND eord.lifnr LIKE 'IVG%'
AND zsm2.werks = STUFF(eord.lifnr,1,3,'')


And you may as well put:

AND mbew.bwkey = zsm2.werks


Your types look like they need some attention, and of course your indexing strategy is bound to need some attention.

EDITED FOLLOWING PLAN UPLOAD

So, now that I've seen the plan. Yeah, it's ugly.

You should consider what is going to be a useful method for joining your main tables, and how you'd do this if you were solving it on paper.

Try indexes on both Order_Detail and Order_Header, on (SourceID, OrderID), including all the other columns that are needed. This will allow a Merge Join between those tables. Then making sure that you can quickly access the rows in each subsequent table, and you have a relatively good candidate for a nicer plan.

eg: an index on eord (flitnr, matnr, lifnr) --with the LIKE 'IVG%' thing I mentioned above.

There's going to be a bunch of pain around those join clauses with CASE. If you need it to be joining to exactly one, you may prefer to use OUTER APPLY (SELECT TOP (1) ... WHERE StartDate &lt; CASE... oh.OrderDate...END ORDER BY StartDate DESC), with an index on (SourceID, CurrencyCode, ToCurrencyCode, StartDate DESC)... you don't really need EndDate.

I've seen worse, and at the end of the day, this should be quite tunable.