CODEWITHSUNDEEP
sqldatabasesql-insertdelete-rowcorresponding-records
Tom
Tom

Reputation: 4059

Delete + insert data from/to database

I have some database tables those contains some aggregated data. Their records (some thousand / tables) are recomputed periodically by an external .NET app, so the old data should be deleted and the new should be inserted periodically. Update is not an option in this case.

Between the delete / insert there is an intermediate time, when the records state is inconsistent (old ones are deleted, new ones are not in the table yet), so making select query in that state results an incorrect result.

I use subsonic simplerepository to handle database features.

What is the best practice / pattern to workaround / handle this state?

Upvotes: 1

Views: 188

Answers (1)

Eli Gassert
Eli Gassert

Reputation: 9763

Three options come to my mind:

  1. Create a transaction with a lock on reads until it is done. This only works if processes are relatively fast. A few thousand records shouldn't be too bad if you transact/lock a table at a time -- if you lock the whole process, that could be costly! But if data is related, this is what you'd have to do
  2. Write to temporary versions of the table, then drop old tables and rename temp tables.
  3. Same as above, except bulk copy from temp tables (not necessarily SQL temporary tables, but ancillary holding tables would suffice) into correct tables, first deleting from main table. you'd still want to use a transaction for this.

Upvotes: 3

Related Questions