Skip to content
+1-888-319-3663

COMMUNITY FORUM

Set all SQL rows to sync

Chris asked 5 years ago
I have a map that has MSSQL as the source and MS CRM as the destination.
My query is about setting all existing records in the MSSQL table as ‘sync’ as currently only those created or updated are processed by the map.
Is there any easy way to do this?
Chris
Answers
Jared Dux Staff answered 5 years ago
You could do this with a map task that runs if the document succeeds.  For the task you would choose Run Sql Command and then write an update statement to update your field to ‘sync’ in your sql table.  The statement would be like: update YOURTABLE set YOURFIELD = ‘sync’ where YOURUNIQUEID = ‘_YOURUNIQUEID’
Chris replied 5 years ago

Hi Jared, is the SQL I have a good idea? It seems to work as expected in my test environments.

Chris answered 5 years ago
Hi Jared,
Thanks for the response. Following on from this, I have the following SQL which may be easier if there’s direct access. Would you support this route as we’d have to make changes to the source DB anyway?
  DECLARE @BookingId int = 0
  WHILE (1 = 1)
  BEGIN
  SELECT @BookingId = MIN(BookingId)
  FROM [No1].[dbo].Booking WHERE BookingId > @BookingId
  IF @BookingId IS NULL BREAK
    INSERT INTO [TestDb].[dbo].[sc_gen_Booking_tracking_BOOKINGSACTUAL](sc_MapId,sc_Processed,sc_DateCreated,sc_Action,BookingId)
    VALUES (‘BOOKINGSACTUAL’,0,CURRENT_TIMESTAMP,’CR’,@BookingId)
  END
Thanks,
Chris

If you would like to submit an answer or comment, please sign in to the eOne portal.