Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mfc2mfc2
Starting Member
4 Posts |
 Posted - 2005-04-23 : 19:43:13
|
| Scenario: a large data mart used for reporting needs to be updated nightly while minimizing downtime. The data comes from one massive extract as a text file via FTP. SQL Server 2000.What is the best way to replace the old data with the new data with minimal downtime? If there is any downtime, it is preferred if the affect is a delayed query execution instead of an error such as "table does not exist."Some ideas are:1. Copy the existing table, fill it with the new data, drop the original table, rename the table copy.2. Fill a copy of the main table with the new data, identify any rows that are different in the main table, then update, insert, or delete as needed.What are some other options? What is the best option?Thank you,Mike Chabot |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-04-24 : 03:12:18
|
| 2) will create huge logging effort, and thus probably be quite slow. (Unless only relatviely few rows change)1) sounds better.Is it just ONE table that gets updated?Kristen |
 |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-04-25 : 01:36:28
|
| we did number 1 before for a weekly update on two tables, but in the process, keep forgetting to re-apply the permissions and being routine, the task just consumes time (aside from having to drop and recreate the constraints)in the end, i just created an SP to truncate the table then bcp in the data from the text file and scheduled it to check for new text files then sends email to notify that the bcp is done or if it failed--------------------keeping it simple... |
|
|
|
mfc2mfc2
Starting Member
4 Posts |
Posted - 2005-05-02 : 15:59:11
|
| It is actually quite a large database with many tables that need to be updated. The problem with simply truncating each table and importing data directly is that this action takes a few minutes to complete for some of the tables. During the update time, queries against the tables may fail or return unusual results. |
|
|
|
|
|
|
|
|
Minimize downtime while updating table