Here are the 2 options I mentioned earlier. The recursive CTE has the advantage of returning the results in a single query. The set-based loop (in my experience) is usually...

This is easily achievable with a recursive CTE or a set-based loop[/url]. The issue is that you mention that C doesn't have a parent, but it has one on the...

lsalih (7/18/2016)

---

I am going to look further into it and see what is happening.

Meantime may I ask you how you run two CMDSHELL commands in the same transaction. Do...

Transactions are made to handle changes in the database within a certain connection. Stopping/Starting services...

I didn't remember this when you first posted your problem, but have you tried using SQL#? It has Replace functions that might help you.

http://www.sqlsharp.com/

Do you realize that xp_cmdshell is not affected by explicit transactions?

Ed Wagner (7/18/2016)

---

HappyGeek (7/18/2016)

---

Ed Wagner (7/18/2016)

---

Revenant (7/18/2016)

---

crookj (7/18/2016)

---

whereisSQL? (7/18/2016)

---

djj (7/18/2016)

---

Revenant (7/15/2016)

---

Luis Cazares (7/15/2016)

---

Fell in...

This could give you an idea.

SELECT *

INTO #SampleData

FROM(

SELECT 123456, 789, 'N' UNION ALL

SELECT 123456, 789, 'N' UNION ALL

...

Fell in love

Yes, but that's not the point.

You'll delete once and insert multiple times. That's the way to call them from...

Then maybe, you should create one stored procedure to delete and one to insert.

If you'll always have an array that will have the rows that must remain stored, then you...

Why not use MERGE to do an UPSERT and forget about deleting rows? Just insert or update depending on if a row exists or not.

Or if you don't want to...

Jabba1963 (7/15/2016)

---

Thanks for looking at this Luis...

The good news is that I had already adopted the windows partitioning approach as you suggested - so good to know I was heading...

Broken

I suggest that you follow the link I posted. There's a similar version of your code which runs about 4 times slower than...