thanks for the suggestions, I will try these and update the results.

Can we do updates in batches? If I have total record count of 10 million than what should...

It has a clustered index but clustered index column is not included in where clause nor in the list of columns getting updated.

Does dropping a clustered index before update...

What columns are on the clustered index?

Do you have any other indexes on the table?

You know you could write the two update statements to be just one update statement?

I tried that and it ran (unlike when I put the option in other parts of the code) but I still got the same error. Any ideas as to...

It would help if you could show us the queries with information on the number of rows in each table.

My present solution approach, in Tableau, was predicated on joining primary data table, containing rows representing the submissions, to the result of the query I've been trying to develop...

It doesn't matter what you select...

Why is it difficult to incorporate it into your solution?

Not sure I quite understand what results you want but I think this might do:

DROP TABLE IF EXISTS #tmp2
CREATE TABLE [#tmp2] (
     ...

I would write it using EXISTS instead of INs. It's easier if you use table aliases.

Then look at increasing the performance by adding indexes.

CREATE PROCEDURE dbo.spTest
(
 ...

I tried it and they were about the...

In this case - yes. Because GROUP BY is applied to the output of the SELECT - making...

It explained where the performance improvement comes from.

If you want to see it - just rewrite...

This should speed the query up

IF OBJECT_ID('tempdb..#CTE','U') IS NULL
    DROP TABLE #CTE

SELECT *
  INTO #CTE
  FROM (SELECT Po.GlobalPnId ,
   ...

It explained where the performance improvement comes from.

If you want to see it - just rewrite any of...