We do this using Staging Tables which are a copy of the actual data.
We create the staging table with ONLY the columns that the 3rd party APP needs, and we insert/update ONLY rows that have changed in those columns using a large WHERE clause that checks every column's value.
We add two columns to the start of the staging table:
Status - 1=Inserted/updated, 2=Deleted
We change the [Status] to 2 for any row (currently set to 1) where the PKey columns can no longer be found in the parent table. (And we change 2 back to 1 if the row is found again e.g. tomorrow). (We also set the [Status] to 2 if the record is flagged as Deleted/Inactive, rather than physically deleted i.e. anything which should cause the 2rd party APP to ignore that record)
We do not have separate [Status] values for Inserted/Updated as the remote end may not process an INSERT before we change it to an UPDATE, so we are basically only interested in having a way of telling the Remote end that a record has been deleted.
The remote end can store a "Last processed date" and process everything SINCE that date. However, beware that you might be adding rows to the staging table at the same time as the remote process is selecting them, so the remote process needs to be careful in that situation. Ideally you will update the staging table using an ATOMic process so that the remote sees all-or-none.
Our WHERE clause has two types of comparison, one for String and one for Numbers/Dates/Non-strings. Our String comparison is BINARY (Case/Accent/etc. sensitive) and includes leading/trailing spaces etc. comparison i.e. ANY change is significant.
DATALENGTH(D.[StringColumn]) <> DATALENGTH(S.[StringColumn])
OR D.[StringColumn] <> S.[StringColumn] COLLATE Latin1_General_BIN2
OR (D.[StringColumn] IS NULL AND S.[StringColumn] IS NOT NULL)
OR (D.[StringColumn] IS NOT NULL AND S.[StringColumn] IS NULL)
D.[NumericColumn] <> S.[NumericColumn]
OR (D.[NumericColumn] IS NULL AND S.[NumericColumn] IS NOT NULL)
OR (D.[NumericColumn] IS NOT NULL AND S.[NumericColumn] IS NULL)
We use a rather crude means of updating the staging table: We delete any rows (in Staging Table) where the PKey exists AND one/many columns are different. Then we insert ANY row that is missing (i.e. this includes both New and Modified rows). Using MERGE would be better
Then we change the [Status] to 2 based on a PKey comparison
SET [xxx_UpdateDt] = @dtNow,
[xxx_Status] = 2 -- InActive / Deleted
FROM dbo.MyStagingTable AS D
LEFT OUTER JOIN MySourceTable AS S
ON S.PKeyColumn = D.PKeyColumn
WHERE [xxx_Status] <> 2
S.PKeyColumn IS NULL
OR S.IsActive = 0 -- e.g. if you have an Active/Inactive flag
-- alternative to Row Delete