Stored procedure - problem with merge - Insert I am calling a stored procedure from my c# code . The procedure works fine for update, but fails for Insert. Is the following merge-insert procedure code correct?

Any help is appreciated.

Here is the procedure:

CREATE TYPE TrackType AS TABLE
(
t_Id uniqueidentifier,  t_JobID  nvarchar(50), t_CreatedDate datetime2(7), 
t_ModifiedDate datetime2(7), t_DbDate    datetime2(7)
t_SubGUID nvarchar(MAX), t_eType nvarchar(MAX)
);
GO


ALTER PROCEDURE [dbo].[Process_NO_table] 
@track       TrackType READONLY
AS
// i need to iterate all the rows of the table(over a million)

Declare @rows INT
Declare @i int = 0
Declare @count int = (SELECT COUNT(*) FROM @track)
DECLARE @is INT

  while (@i &lt; @count)
BEGIN


// first i check modified date from the database table 
   SELECT  @is = COUNT(*) FROM NO_table WHERE [JobID] IN (SELECT [t_JobID] FROM 
@track)
MERGE [dbo].[NO_table] AS [Target]
USING @track AS [Source]

// if the database modifed date is less than the modifeid date from the 
proceduretable(@track) then it updates the records
ON [Target].[ModifiedDate] &lt; [Source].[t_ModifiedDate] AND JobID = t_JobID
WHEN MATCHED THEN
    UPDATE  SET [JobID] = [Source].[t_JobID],
        [CreatedDate] = [Source].[t_CreatedDate]
  [DbDate]= [Source].[t_DbDate]
 [ModifiedDate] = [Source].[t_ModifiedDate]
[SubGUID] = [Source].[t_SubGUID] 
 [eType] = [Source].[t_eType]

// if there no records with the same jobid then it creates-insert a records
MERGE [dbo].[NO_table] AS [Target]
USING @track AS [Source]
ON (@is != 0)
WHEN NOT MATCHED THEN
   INSERT INTO [NO_table] ( [JobID], [CreatedDate], [ModifiedDate], [DbDate], 
[SubGUID], [eType]  )
VALUES ( [Source].[t_JobID], [Source].[t_CreatedDate], [Source].[t_ModifiedDate], 
[Source].[t_DbDate], [Source].[t_SubGUID], [Source].[t_eType] )
;


SET @i = @i + 1

END

GO
How about simplifying greatly:

UPDATE d SET 
  [CreatedDate] = [Source].[t_CreatedDate],
  [DbDate]= [Source].[t_DbDate],
  [ModifiedDate] = [Source].[t_ModifiedDate],
  [SubGUID] = [Source].[t_SubGUID],
  [eType] = [Source].[t_eType]
FROM dbo.NO_table AS d
INNER JOIN @track AS [Source]
ON d.JobID = [Source].t_JobID
WHERE d.[ModifiedDate] &lt; [Source].[t_ModifiedDate];

INSERT dbo.NO_Table(...cols...)
  SELECT ...cols...
  FROM @track AS t
  WHERE NOT EXISTS (SELECT 1 FROM dbo.NO_Table 
    WHERE JobID = t.t_JobID
    AND [ModifiedDate] &gt;= [Source].[t_ModifiedDate]);


Now you have two set-based operations instead of over a million singletons, and you avoid doing silly things like counting rows when you only care if it the result is zero or non-zero. And you avoid the overkill of MERGE for this simple scenario (and a bunch of other things that come with it).