That's a decent size table 😉

I would shove any intimidation I had about such a large table and press forward with tuning my query.  I'd probably start with getting a...

Sounds like a candidate for a temporal table.  Have you checked that out?

IMO, the tabular model should be kept simple.  You want it be easily consumed by BI users without a ton of modeling transformations.  I would look hard at the execution...

Thanks Jeff!

The engine is smart enough that either is fine.  You could take a look at the execution plans and statistics on both versions of your query.

Because you're taking the output of your merge and inserting into scdmain.  There are NULLs being inserted into scdmain whenever you have rows in target that are not matched by...

Just curious as to why?  Making sure I'm not missing out on something 😉

Are you aware of the report execution log (a bit different than error logging) - dbo.ExecutionLog3

This makes more sense!  Your error message indicates a constraint violation on insert.  That is probably happening on scdmain.  It's probably source.id which would be NULL.  Do you have a...

wow, that is interesting!

I would write an update query (similar to what I showed you) before the merge rather than spend a bunch of time trying to solve this all...

Nice!  Great job!!

You're saying that without the "when not matched by source" clause it works?  That would be interesting since the error message is referring to an insert action.  Do you have...

Gotcha.  I went back to SSMS (rather than pull something I already had)...how about this?  Is this what you need?

declare @t table (cola int, IsActive bit)
declare @s...

Are you executing the query in SQL Server Management Studio or something else?

Did you look at the code I gave you?  I wanted to show you how I handle the same situation you are facing.

Make sure the table exists and that the user executing the query has SELECT access to it.

As a side note...IMO, it's not a good practice to use spaces in your...