T-SQL Stored Procedure to Insert/Update changes and if successful set column processed to Y

I'm synchronizing two applications and ERP and CRM. I'm using a table for changes with a column to mark Y after processed and a stored procedure that will run on a 15 minute interval with the sql server agent.

When I run the batch I need to check for any missed records in the event that the last batch batch didn't run. I need a way to determine how to check that the insert completed and then set the processed column to yes. It must be something like if insert completes then set processed column to Y, else do nothing and report error.

We have customers in the crm, so when the names and address are updated we're going to change them in the erp. I have a trigger on the costumer contacts table and when the customer status is changed it will add the customer name record to the contactschanges table. On a period of 15 minutes we will insert new customers or update customers in the erp contacts. What we have is that sometimes the insert/update doesn't run so we need to check for any missed update batch and update. To keep track of any missed insert/updates there is a Processed column that can be set to Y after the record is inserted/updated.

What we need to do is make sure that the records are posted from the crm to the erp, we have two processes that can fail, 1. the sql agent doesn't execute the stored procedure that inserts the records, or 2. there is some error inserting the records and it doesn't complete.

I need the stored procedure for the following logic

If this statement suceeds

SET IDENTITY_INSERT CONTACTS ON

INSERT INTO CONTACTS

(CODE, ID, CODE_ID, FIRSTNAME, MIDDLENAME, LASTNAME, ADDR, DECEASED_FLAG, CREATEDATE, CREATETIME, IDSeq)

SELECT [login to view URL], [login to view URL], CC.CODE_ID, [login to view URL], [login to view URL], [login to view URL], CC.CREATE_DATE, CC.CREATE_TIME, [login to view URL]

FROM CONTACTSCHANGES AS CC INNER JOIN

CONTACTS AS C ON [login to view URL] = [login to view URL]

SET IDENTITY_INSERT CONTACTS OFF

then do this

UPDATE CONTACTSCHANGES

SET Processed = 'Y'

else

do nothing, report error, if the update didn't complete the next time it runs will try again.

I need the answer in 8 hours

Please reply with what time of day you are available. I am available 7am to 5pm utc - 6:00

SQL Server 2008r

Microsoft SQL Server

ID do Projeto: #13824207

Sobre o projeto