Database SQL Script very, very slow (string manipulation over 700k rows)
You have a performance question so I recommend you read How to analyse SQL Server performance to begin with.

As about your script: you're doing it as slowly as possible. Row by painfully slow row. Not even a cursor slow, is worse than a cursor slow. What you have here is a single update:

update original_test_dataset
set duplicate= case when PATINDEX('%dup%', id)=0 then 0 else 1 end,
 id_only = REPLACE((LEFT(REPLACE(id,'rec-',''), CHARINDEX('-',REPLACE(id,'rec-',''),0)),'-','');


That's it, nothing else.

What you have in the original script is slow because:


it operates on row by row as opposed to sets. SQL is designed to manipulate sets, not rows.
it perform a lot  of writes in standalone commits resulting in huge flush commit waits. Batch commit should be used.
it performs multiple unindexed end-to-end scans on  #ControlTable (every operation on  #ControlTable is an unindexed end-to-end scan, it should had an index on id, yes #temp tables do need indexes)
it does two updates per row instead of one update of two fields
I can't know whether the access to original_test_dataset is in any way optimized, in lack of an index on id is likely is not.


declare @TableID varchar(20) vs.  [id] [varchar](50) NULL: you're silently truncating data. Basically, your script corrupts the table.