I have a SQL process, which involves ingesting an XML file, and either inserting the record or updating an existing record ([changetype] = 'I' or 'U)'. This record is then pulled into an external system, and updated to a doc management system (Processed gets set to the datetime and this runs every 15 minutes).
I use the below Stored Procedure to check if the record exists, and either insert the record if it doesn't exist (and set change type to 'i') or update the record if it does exists (and set changetype to 'u').
The problem is we sometimes get an insert and update record at the same time, and the update overwrites the insert. This stops the record from being ingested, as there is no existing record to update. What i'm thinking is having an If/Else statement, where If 'Processed IS NULL' it updates everything but sets changetype to 'i', Else it updates as it currently does. Not sure if that makes any sense.
IF NOT EXISTS (SELECT 1 FROM database Where ClaimReference = @ClaimReference)
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;-- Insert statements for procedure here INSERT INTO database (ClaimReference, CoverNumber, ClientNumber, ClientName, Department, Source, Consumer, SecurityGroup, RiskDescription, PolicyNumber, Insurer, PolicyDepartment, PolicyYear, Claimant, ClaimType, DateOfLoss, VehicleNumber, ChangeType, ClaimsHandler, DateofChange, Processed, ERROR, ERROR_MESSAGE) VALUES (@ClaimReference, @CoverNumber, @ClientNumber, @ClientName, @Department, @Consumer, @SecurityGroup, @RiskDescription, @PolicyNumber, @Insurer, @PolicyDepartment, @PolicyYear, @Claimant, @ClaimType, @DateOfLoss, @VehicleNumber, 'i', @ClaimsHandler, GETDATE(), NULL, '0', NULL)
END
ELSE
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;-- Insert statements for procedure here UPDATE database SET ClaimReference = @ClaimReference, CoverNumber = @CoverNumber, ClientNumber = @ClientNumber, ClientName = @ClientName, Department = @Department, Consumer = @Consumer, SecurityGroup = @SecurityGroup, RiskDescription = @RiskDescription, PolicyNumber = @PolicyNumber, Insurer = @Insurer, PolicyDepartment = @PolicyDepartment, PolicyYear = @PolicyYear, Claimant = @Claimant, ClaimType = @ClaimType, DateOfLoss = @DateOfLoss, VehicleNumber = @VehicleNumber, ChangeType = 'u', ClaimsHandler = @ClaimsHandler, DateofChange = GETDATE(), Processed =NULL, ERROR ='0', ERROR_MESSAGE =NULL WHERE ClaimReference = @ClaimReference
END