How to keep track of which rows have been imported in SQL?

Let's say I want to import all the customers (or all the rows in some other specific table) to some external system. Not all at once but every one after they have been created in database. To do that I have to keep record of all the rows that have already been reported because I want to find only the ones that have not been reported yet. Is it generally better to add开发者_JS百科 a column to do that or to create some kind of a batchlog table?

I'm using MS SQL Server if that is relevant

A Simplified example:

select * from Customer where reportedToExternalSystem is null

or

select * from Customer where cus_id not in (select cus_id from integrationBatchLog)

or is there maybe some more ways to do that that might be even better? This is the first time I do something like this so I don't know the best practise yet.

---

The simple solution is to add a column that marks the row as imported. A status int (0/1) or if you want to keep track of when it was imported an imported date. This solution does have some limitations:

1. You can only import the row once. Do you need to import the customer again when the record is updated? Are you going to clear the update field when the customer is updated?

2. It causes the row to be locked when you update the row status. Are you sure the application that inserts the customer record will be happy with your code locking the records?

3. On some system it causes the entire row to be written to the log system for recovery. Depending on the size of the row this can be a lot of log writing for just one field.

4. In a highly parallel import system you can have a lot of contention for resources. If one import program is locking the table, think how bad it would be if many import programs are locking the table at the same time.

5. If the customer data is updated several times between your import polling interval, you will only see the latest data and will skip over the intermediate updates. This is only an issue if you care about the intermedaite updates. For customers you might not care, for order statuses you might care a lot.

6. You have to modify the table structure. This might not be allowed by the source application due to data/support/political issues.

Besides putting a status column in the table, one technique that works well is to put a trigger on the table and mirror the import data to a second table. You would then 'consume' the data in the second table. This has several advantages:

1. It keeps the locking issues contained to the second table.

2. It allows you to process every update to the main table.

3. You can add an index to the second table that is used to keep track of the update statuses without the issues of changing the main table.

4. If you delete the rows from the second table (either immediately as they are consumed or after a short audit period) the size of the table/index will be kep to a minimum.

When I use this technique in Sql Server I put the second table in a seperate schema. Since most apps store their tables in dbo, you can end up with dbo.Customers and Import.Customers. This can help you to keep track of which tables you are importing and keeps you from having to come up with new names for your import tables.

---

Unless you have to complicate implementation, go with the simplest solution possible. One important thing you should consider, is how hard would it be to refactor this simple to more general one, in case if you need it.

In your case I see only one problem in upgrading from column to table. If you would need history of imports. Solution: make reportedToExternalSystem column of DateTime (or Timestamp) type

---

I would use a separate table indicating, say, import date cross-referenced to the key of the record in the table you're tracking. In other words, a table with 3 columns: auto-increment key, record-id-from-other-table, import-date. Something like that. This also allows the case if a record is ever re-imported later. You'd have track of all the imports by date.

---

I Prefer having a column for importing status. Maintaining a separate log leads to time consumable results with growing table size. I do have conceptual idea on SQL Servers but seems that it works. Keep posting!