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
MERGE
for these operations if each statement only does an update or an insert?UPDATE
and anINSERT
...