Insert Large Amount of Data in a table using by online users

• Loner

  SSC-Insane

  Points: 21279

  More actions

  January 18, 2008 at 8:45 am

  #69705

  I have a table that is used by users during the day. Also this table will be updated when we receive data from outside vendors during the day couple of times and a couple millions of rows each time. I use bcp to do the insert but still it will slow down the online users'. Does anyone have a better way to handle this?

  Should I have a shadow file for update and switch to the real table when it is done?

  Thanks

• Steve Jones - SSC Editor

  SSC Guru

  Points: 737424

  More actions

  January 18, 2008 at 9:01 am

  #769733

  It depends on your process and business rules. I've seen this handled with shadow tables, slowing down the updates (smaller batches), or even adding to the table as new rows, but including a join to a "live" record in another table. Add a live record when the new data is ready, remove the old "live" record.