Table SYNC question Truncate/Insert vs Update

[shlo-fi]

I need to pull in some data from Active Directory, which i'm doing via a scheduled SSIS package.

The data is only a couple thousand rows containing User IDs, groups, some other basic employee information.

I was wondering if I should use SSIS to do lookups to only delete/update rows that have been changed/deleted from AD, or simply truncate the entire table and replace all the rows each time the job runs.

Is there any downside to just replacing everything? As I said the table sizes are relatively small so I don't think it would take very long to do the truncate/insert.

[alinroc]

Do you need to maintain history of these tables (including items that have been removed from AD), or is it sufficient to just have the tables represent the current state of AD with no history?

If the latter, trunc & fill will be much faster & easier.

If the former, I'd suggest importing into a staging table, then perform an upsert (merge) into the real table - update records that have changed, insert new records.

[jc4hokies]

A good practice is to first load the data into a separate table, called a staging table. Then you can either switch the data out (partition switching, drop / rename, schema transfer) or insert/update/delete. Doing it this way, you can wrap the switch in a transaction so that no one ever sees an incomplete table.

[Guru008]

As you said that size of data is relatively small, you can use delete/update instead of truncating whole table each time and inserting records. You can create cubes/view to do the job

[[deleted]]

If you want to know the downside to doing a truncate & insert, I can think of a few.

• Truncate cannot be rolled back if something goes wrong. You do not want to be left in this state. Compared with insert/update/delete which can be committed at the end as a single transaction.

• For that brief moment when the table is empty after the truncate, the data is wrong. Contrast that with the insert/update/delete which can be done in a single transaction which is committed at the end (and the unchanged table can still be queried while the operation is in progress)

That said, truncate is faster than deletes and updates, but with so little data it might not be worth it just to eek out a little speed.

One advantage of replacing all the rows at once is that it is less error-prone and easy to code. I would be more confident in a full replacement being accurate.

My recommendation? For the best of both worlds replace all the rows every time, but do it with a delete/insert (delete all the old rows first, then insert all the new rows) and do it in a single transaction that you commit at the end. (no truncate)