Set all SQL rows to sync
Chris asked 6 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
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 6 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 6 years ago Hi Jared, is the SQL I have a good idea? It seems to work as expected in my test environments.
Chris answered 6 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
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