0

In my application (C# application, using Entity-Framework and SQL Database), I am needed to create a daily task to update/insert data from a third-party application (both the applications are using SQL server database). For efficiency sake, I am looking for a way to determine what all records from the previous day have been modified and thus import only those records.

I know I can add a modified_on column to the source table and create a trigger to update that column when something is changed on that record, but that will need me to make changes to the third-party application's database schema which I want to avoid.

Dale K
  • 25,246
  • 15
  • 42
  • 71
arashutosh
  • 9
  • 3

2 Answers2

1

There's the change tracking feature but it's of limited use to you as you're using EF and that makes the way the data is queried awkward. You may be able to use it somehow, but I doubt it's elegant.

Way easier is to indeed change the schema but add only a single column of type rowversion. That binary datatype (loaded as byte[] in EF) is special and gets larger every time something (such as the third-party application) updates the row. No need for any triggers. You can look what the largest one is you already processed and then query all those that are larger than that.

John
  • 6,693
  • 3
  • 51
  • 90
0

In addition to change tracking suggested by John, in another answer, you can think of setting up Temporal tables.

You can run queries against the temporal tables to identify the changed records and pull them accordingly from main table.

Venkataraman R
  • 12,181
  • 2
  • 31
  • 58