Here is the script to have data at initial stage. Here table Target acts as SCD type 2 table

IF OBJECT_ID('tempdb.dbo.#Source', 'U') IS NOT NULL
  DROP TABLE...

Off course. Here it goes.

Source table contains data like this:

EmpId attr1 attr2 attr3 attr4 effStartDate effEndDate

E1001 abc xyz knop sts '2015-06-01 00:00:00.000' '9999-12-31 00:00:00.000'

E1002 vah sje jshu kjd '2015-01-01 00:00:00.000'...

Mr or Mrs 500, i was able to get that information using substring. Its the other part where i have to make up the value and join that value with...

I need query to get value of col_abc3 for year 2019 and month 2 based on value of col_processed which is abc2.

But why an Insert based error when all i am trying to do an update

Yeah i know Heb. Let me post my complete query. Its like this (pls check if there's something wrong):

Insert into scdmain(

Id, col1, col2, col3, scdstartdate, scdenddate

)

Select Id, col1, col2, col3,...

In addition, if i mention "when not matched by source then delete", it works like a charm and deletes the record which is present in target but not in source.

No trigger. Even i was surprised by this error. When i remove this "when not matched by source", it runs smoothly. Just that the record which is present in target...

Exactly that's what i am looking for but "when not matched by source" is not working for the UPDATE. It throws error like "cannot insert the value NULL into column...

Yes i checked and basically you are updating the status before the merge statement. Is it possible to do all in one single merge statement ?

Yes, i am talking about same soft delete. However my following condition is not working:

When not matched with source and is_latest = 'Y' Then update is_latest = 'N', SCDEndDate =...

Hi Heb, i dont want to delete anything from target table. My source table is a truncate and load feed. Once source is loaded properly, then only other process will...

Sorry guys for late reply as i had to move out of town on an emergency. As of now there is no solution but i am going through the link...

I will definitely check that link in details. In between, to get optimized results feel free to make changes from these two temp tables to new temp tables in the...

• This reply was modified 5 years, 6 months ago by  sqlenthu 89358. Reason: Updated with more clarification

Hey Jeff, there ID column is simply identity column. Its the businessID and Hierarchy column which are related. So IDs 2, 3, 4 have same hierarchies because businessid 10, 11,...