Stored procedure - problem with merge - Insert

Question

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 < @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] < [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

Answer 1

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] < [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] >= [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).

Answer 2

Merge is actually valuable for what the OP is trying to do, because UPDATE is highly inefficient, as well as committing an Update then Insert separately could have a concurrent write between commands that the Db may misassign rows; i.e. it is non deterministic at times.

What merge does is do an upsert, but it only hits each row ONCE. it also errors on mutli-hits, so you can investigate using try catches to single out bugs later on.

It is extremely more efficient that the counterparts, but does defy standard procedural/Loop logic (I'm .Net before SQL so I understand the slight confusion). Merge is the loop, like a pre-generated For loop that you have a select case of 1-7 options within; each iteration will hit and succeed or error only once, and an error will break the process. The two step logic is actually implemented across the join and where with the on statement needing to address no variables or constants, but rather table keys. The date logic can be addressed on matching, since the date does not matter on a missing JobId. Merge is not difficult, and could simply be implemented this way:

Merge into [dbo].[NO_Table] Target
Using @target Source
on target.JobId = Source.t_JobId
when matched and target.ModifiedDate < Source.t_ModifiedDate then
update set
[CreatedDate] = [Source].[t_CreatedDate],
[DbDate]= [Source].[t_DbDate],
[ModifiedDate] = [Source].[t_ModifiedDate],
[SubGUID] = [Source].[t_SubGUID],
[eType] = [Source].[t_eType]
when not matched then
insert([CreatedDate], [DbDate], [ModifiedDate], [SubGUID], [eType])
values(
[t_CreatedDate],
[t_DbDate],
[t_ModifiedDate],
[t_SubGUID],
[t_eType]);

-- you could put in an OUTPUT following not matched, and use $action to test for logic 
-- by action. 

If you administer an event log or such, you can output row data from target, source, and any generated columns to a table (for good forking of output), which cannot be easily accomplished on traditional with output in insert and update ops. I use merge all the time now, it is wonderful, since it grows with the table (might mostly insert at first, then trend to update) plus it lets you take TVPs and run wild; I hope this gives a decent perspective on the other side of the fence, and possibly a much more rapid appraoch to processing 1 mil+ rows