Try this instead of the cursor. You will see in the script a WAITFOR statement. You can set this to cause a delay between each batch run. I set it for 10 seconds but you can set it for whatever is best to keep your server happy.

Again, start with sample code. This time I'm using a regular table and I added another batch.

/* Create a source table for testing */

/* Do this ONCE */

CREATE TABLE dbo.TempSourceTable (

[UniqueKey] INT IDENTITY(1,1) NOT NULL,

[id] INT NOT NULL,

[RecTime] SMALLDATETIME NOT NULL,

[value] SMALLMONEY,

[flag] SMALLINT,

[max_value] SMALLMONEY,

VARCHAR(500),

PRIMARY KEY([UniqueKey],[id],[RecTime]));

GO

INSERT INTO dbo.TempSourceTable

VALUES

(42, '2008-04-08 18:00:00', 945.00, 4, 1080.00, ''),

(42, '2008-04-08 18:20:00', 948.00, 4, 1080.00, ''), -- 20min

(42, '2008-04-08 20:00:00', 945.00, 4, 1080.00, ''), -- 100min

(42, '2008-04-08 20:20:00', 943.00, 4, 1080.00, ''), -- 20min

(42, '2008-04-08 22:00:00', 945.00, 4, 1080.00, ''), -- 40min

(42, '2008-04-08 22:20:00', 947.00, 4, 1080.00, ''), -- 20min

(42, '2008-04-09 00:00:00', 948.00, 4, 1080.00, ''), -- 100min

(42, '2008-04-09 00:20:00', 934.00, 4, 1080.00, ''), -- 20min

(42, '2008-04-09 02:00:00', 931.00, 4, 1080.00, ''), -- 40min

(42, '2008-04-09 02:20:00', 918.00, 4, 1080.00, ''), -- 20min

(42, '2008-04-09 04:00:00', 900.00, 4, 1080.00, ''), -- 100min

(42, '2008-04-09 04:20:00', 911.00, 4, 1080.00, ''), -- 20min

(42, '2008-04-09 04:30:00', 923.00, 4, 1080.00, ''), -- 10min

(42, '2008-04-09 07:30:00', 923.00, 4, 1080.00, ''), -- 10min

(42, '2008-04-09 07:40:00', 923.00, 4, 1080.00, ''), -- 10min

(42, '2008-04-09 08:10:00', 923.00, 4, 1080.00, ''), -- 10min

(4429, '2008-04-08 17:30:00', 2348.00, 4, 3340.00, ''),

(4429, '2008-04-08 18:00:00', 2354.00, 4, 3340.00, ''), -- 30min

(4429, '2008-04-08 19:48:00', 2399.00, 4, 3340.00, ''), -- 108min

(4429, '2008-04-08 20:00:00', 2395.00, 4, 3340.00, ''), -- 12min

(4429, '2008-04-08 22:00:00', 2345.00, 4, 3340.00, ''), -- 120min

(4429, '2008-04-08 22:38:00', 2344.00, 4, 3340.00, ''), -- 38min

(4429, '2008-04-08 23:05:00', 2297.00, 4, 3340.00, ''), -- 27min

(4429, '2008-04-08 23:09:00', 2408.00, 4, 3340.00, ''), -- 4min

(4429, '2008-04-08 23:31:00', 2453.00, 4, 3340.00, ''), -- 22min

(4429, '2008-04-09 00:00:00', 2436.00, 4, 3340.00, ''), -- 29min

(51, '2009-10-22 18:00:00', 945.00, 4, 1080.00, ''),

(51, '2009-10-22 18:20:00', 948.00, 4, 1080.00, ''), -- 20min

(51, '2009-10-22 20:00:00', 945.00, 4, 1080.00, ''), -- 100min

(51, '2009-10-22 20:20:00', 943.00, 4, 1080.00, ''), -- 20min

(51, '2009-10-22 22:00:00', 945.00, 4, 1080.00, ''), -- 40min

(51, '2009-10-22 22:20:00', 947.00, 4, 1080.00, ''), -- 20min

(51, '2009-10-23 00:00:00', 948.00, 4, 1080.00, ''), -- 100min

(51, '2009-10-23 00:20:00', 934.00, 4, 1080.00, ''), -- 20min

(51, '2009-10-23 02:00:00', 931.00, 4, 1080.00, ''), -- 40min

(51, '2009-10-23 02:20:00', 918.00, 4, 1080.00, ''), -- 20min

(51, '2009-10-23 04:00:00', 900.00, 4, 1080.00, ''), -- 100min

(51, '2009-10-23 04:20:00', 911.00, 4, 1080.00, ''), -- 20min

(51, '2009-10-23 04:30:00', 923.00, 4, 1080.00, ''), -- 10min

(51, '2009-10-23 07:30:00', 923.00, 4, 1080.00, ''), -- 10min

(51, '2009-10-23 07:40:00', 923.00, 4, 1080.00, ''), -- 10min

(51, '2009-10-23 08:10:00', 923.00, 4, 1080.00, '') -- 10min

GO

/* End of sample source data input */

Create a table to hold the final results.

/* Create a table for the final combined results */

/* Do this ONCE */

CREATE TABLE [dbo].[FinalResultTable](

[UniqueKey] [int] IDENTITY(1,1) NOT NULL,

[id] [int] NOT NULL,

[RecTime] [smalldatetime] NOT NULL,

[value] [smallmoney] NULL,

[flag] [smallint] NULL,

[max_value] [smallmoney] NULL,

[varchar](500) NULL,

CONSTRAINT [PK__FinalRes__06B77A925892CFA9] PRIMARY KEY CLUSTERED

(

[UniqueKey] ASC,

[id] ASC,

[RecTime] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

Now create a procedure to to do the actual work.

CREATE PROCEDURE dbo.RunBatchProcessCalculations

@ProcessID INT

AS

BEGIN

SET NOCOUNT ON

/*

EXEC dbo.RunBatchProcessCalculations 42

EXEC dbo.RunBatchProcessCalculations 4429

*/

IF OBJECT_ID('tempdb..#InterimTable') IS NOT NULL

DROP TABLE #InterimTable

IF OBJECT_ID('tempdb..#CalcTable') IS NOT NULL

DROP TABLE #CalcTable

IF OBJECT_ID('tempdb..#ResultTable') IS NOT NULL

DROP TABLE #ResultTable

/* A working table to hold the results of each batch */

CREATE TABLE #InterimTable (

[UniqueKey] INT NOT NULL,

[id] INT NOT NULL,

[RecTime] SMALLDATETIME NOT NULL,

[value] SMALLMONEY,

[flag] SMALLINT,

[max_value] SMALLMONEY,

VARCHAR(500),

PRIMARY KEY([UniqueKey],[id],[RecTime]));

/* Another working table for holding the */

/* results of the duration calculations. */

CREATE TABLE #CalcTable (

[UniqueKey] INT NOT NULL,

[id] INT NOT NULL,

[RecTime] SMALLDATETIME NOT NULL,

[value] SMALLMONEY,

[flag] SMALLINT,

[max_value] SMALLMONEY,

VARCHAR(500),

PRIMARY KEY([UniqueKey],[RecTime]));

/* A working table to hold the output for each batch */

CREATE TABLE #ResultTable (

[UniqueKey] INT IDENTITY(1,1) NOT NULL,

[id] INT NOT NULL,

[RecTime] SMALLDATETIME NOT NULL,

[value] SMALLMONEY,

[flag] SMALLINT,

[max_value] SMALLMONEY,

VARCHAR(500),

PRIMARY KEY([UniqueKey],[id],[RecTime]));

DECLARE

@NextUniqueKey INT

/* Get the next unique key value */

SELECT @NextUniqueKey = MAX(UniqueKey)+1 FROM dbo.TempSourceTable WHERE UniqueKey > 0

TRUNCATE TABLE #InterimTable

TRUNCATE TABLE #CalcTable

/* Get a working copy of each set so we don't */

/* have to make changes to the source table. */

INSERT INTO #InterimTable

(UniqueKey,id,RecTime,value,flag,max_value,note)

SELECT

UniqueKey

,id

,RecTime

,value

,flag

,max_value

,note

FROM

dbo.TempSourceTable

WHERE

ID = @ProcessID

/* Calculate the durations and create new rows where necessary. */

INSERT INTO #CalcTable

([UniqueKey],[id],[RecTime],[value],[flag],[max_value],)

SELECT

ROW_NUMBER() OVER (ORDER BY [RecTime])+(@NextUniqueKey) AS UniqueKey

,[id]

,(CASE

WHEN DATEPART(minute,[RecTime2]) = 0 THEN DATEADD(hour,1,RecTime2)

WHEN DATEDIFF(minute,[RecTime2],[RecTime]) > 120 THEN DATEADD(hour,-1,DATEADD(minute,DATEPART(minute,RecTime),RecTime))

WHEN DATEDIFF(minute,[RecTime2],[RecTime]) > 60 THEN DATEADD(hour,-1,RecTime)

WHEN DATEDIFF(minute,[RecTime2],[RecTime]) = 60 THEN DATEDIFF(minute,[RecTime2],DATEADD(minute,-DATEPART(minute,[RecTime]),[RecTime]))

ELSE DATEADD(minute,-DATEPART(minute,[RecTime]),[RecTime])

END) AS [RecTime]

,[value]

,[flag]

,[max_value]

,(CASE

WHEN DATEPART(minute,[RecTime2]) = 0 THEN 60

WHEN Diff >= 120 THEN DATEDIFF(minute,[RecTime2],[RecTime])

WHEN Diff > 60 THEN Diff-60

WHEN Diff = 60 THEN DATEPART(minute,[RecTime])

ELSE Diff

END) AS Note

FROM

(

SELECT

t1.UniqueKey

,t1.id

,t1.[RecTime]

,t1.[value]

,t1.[flag]

,t1.[max_value]

,t2.[RecTime] AS [RecTime2]

,ISNULL(DATEDIFF(minute,t2.[RecTime],t1.[RecTime]),0) AS Diff

FROM

#InterimTable t1

LEFT JOIN

#InterimTable t2

ON t1.UniqueKey = t2.UniqueKey + 1

WHERE

t1.UniqueKey > 0

AND t2.UniqueKey > 0

) R

WHERE

Diff >= 60

ORDER BY

[RecTime]

/* Insert the new rows into the interim working table. */

INSERT INTO #InterimTable

SELECT

UniqueKey

,id

,RecTime

,value

,flag

,max_value

,(CASE

WHEN note > 60 THEN '+'+note

ELSE '*'

END) AS note

FROM

#CalcTable

WHERE

UniqueKey > 0

/* Insert the data into the output table */

/* sorted by datetime with new unique keys. */

INSERT INTO #ResultTable

SELECT

id

,RecTime

,value

,flag

,max_value

,note

FROM

#InterimTable

WHERE

UniqueKey > 0

ORDER BY

RecTime

SELECT

id

,RecTime

,value

,flag

,max_value

,note

FROM

#ResultTable

WHERE

UniqueKey > 0

AND id = @ProcessID

ORDER BY

[id],[RecTime]

END

Finally, this script will run each batch after the specified time interval and return status messages to the SSMS Messages window.

The messages will look like this:

Beginning process for batch 42

...Processing complete for batch 42

Waiting 10 seconds before processing batch 51

...Processing complete for batch 51

Waiting 10 seconds before processing batch 4429

...Processing complete for batch 4429

SET NOCOUNT ON

TRUNCATE TABLE dbo.FinalResultTable

/* Get the Process IDs */

IF OBJECT_ID('tempdb..#BatchTable') IS NOT NULL

DROP TABLE #BatchTable

CREATE TABLE #BatchTable (

[UniqueKey] INT NOT NULL,

[ID] INT NULL,

PRIMARY KEY (UniqueKey))

INSERT INTO #BatchTable

SELECT

ROW_NUMBER() OVER (ORDER BY ID) AS UniqueKey

,R.ID

FROM

(

SELECT DISTINCT

ID

FROM

dbo.TempSourceTable

) R

DECLARE

@ProcessID NVARCHAR(1000)

,@rowCount INT

,@UniqueKey INT

,@msg VARCHAR(100)

SELECT @rowCount = COUNT(ID) FROM #BatchTable

SELECT @UniqueKey = MIN(UniqueKey) FROM #BatchTable

SELECT @ProcessID = ID FROM #BatchTable WHERE UniqueKey = @UniqueKey

SET @msg = 'Beginning process for batch '+@ProcessID

RAISERROR(@msg,10,1) WITH NOWAIT

WHILE @UniqueKey <= @rowCount

BEGIN

SELECT @ProcessID = id FROM #BatchTable WHERE UniqueKey = @UniqueKey

IF @UniqueKey > 1

BEGIN

SET @msg = 'Waiting 10 seconds before processing batch '+@ProcessID

RAISERROR(@msg,10,1) WITH NOWAIT

WAITFOR DELAY '00:00:10'

END

INSERT INTO dbo.FinalResultTable

EXEC dbo.RunBatchProcessCalculations @ProcessID

SET @msg = '...Processing complete for batch '+@ProcessID+CHAR(13)

RAISERROR(@msg,10,1) WITH NOWAIT

SET @UniqueKey = @UniqueKey + 1

END

--display the results

SELECT * FROM dbo.FinalResultTable