Got it! Thank you! Now I can post readable code finally! 🙂

All good points Paul. Thanks I will address. I did forget StayDate in the Update. "Where" must be the same in both branches. I did create a unique index on...

Thanks for the tip on posting code! THAT makes quite a difference!! 😀

So below is the rough draft. Look good? Still have some defensive coding to do of...

Perfect! Solid stuff as usual. Thanks once again Paul!

I was just checking out MERGE in BOL. BOL used to give version info. in the docs., but I don't see that...

Lets try this one more time. This post is definitely a sign it's time for bed!!

I'm writing a TSQL sproc that will be called by client applications to...

Sorry about that Paul! I just got done editing the post.

I bailed out in the middle of my posting and jumped over to another forum to ask how others are...

As a matter of fact, I happen to know it is a 2U server. I believe you're right that I won't be able to convince anybody. It stems from the...

Argh! BUT if I implement this in SSIS, I can keep the load off the SQL Server, and since I will want this to be a sort of "service monitor"...

Hmm, wait a minute. If I go the sqlclr route, maybe I don't have to do impersonation? I already have all the linked servers setup. Could it be that simple?

So I'm thinking I like the sound of that sqlclr solution best, IF I can get it to work the way I need!

If I can get "impersonation" to work...

Thanks again Paul. Another great answer.

In my research on this, I came across an SSIS / WMI solution that might be better. I have to actually do this execute this...

Thanks Jeff. actually, I've been doing a little research, I might be better off using SSIS here. I think I understand how to get the service state using SSIS/WMI, but...

WOW! Just got done comparing the NOT EXISTS vs. IGNORE_DUPS on a couple million rows on the real data, NOT EXISTS took about 9 mins., IGNORE_DUPS took 25 MINS. That's...

With EXCEPT, do all the columns have to match to be excluded? The EXCEPT names all the same columns as the select. I guess I would expect to only have...

Thanks Paul! I will definitely give these alternatives a shot and run some benchmarks. Like you said though, the IGNORE_DUP_KEY sure SEEMS like a silver bullet on the surface!

MERGE and...