1

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
  • Define "fails" - does it throw an error, does it just not insert the data? – JNK Dec 8 '14 at 18:26
  • It just doesn't insert the data!It only updates the data.. – user3516668 Dec 8 '14 at 18:39
  • 1
    Why are you using MERGE for these operations if each statement only does an update or an insert? – JNK Dec 8 '14 at 18:45
  • 1
    I would just use an UPDATE and an INSERT... – JNK Dec 8 '14 at 18:49
  • 2
    Why are you doing this one row at a time? – Aaron Bertrand Dec 8 '14 at 19:08
4

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).

| improve this answer | |
  • thx. I wanted to do update when the target modified date of the records is less than the source modifed date where target.jobid= source.jobid.... and insert if the result is zero...so i dont need looping? ..does this work as batch when there are half a million rows in the table? – user3516668 Dec 8 '14 at 19:30
  • 1
    @user so add a WHERE clause, and yes this works as a batch with <insert any number here>. What does not work is processing a million rows one a time. This is often called "death by a thousand cuts" and the imagery is meant to scare you. – Aaron Bertrand Dec 8 '14 at 20:29
0

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

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.