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 |
|
lotek
Starting Member
42 Posts |
 Posted - 2008-01-30 : 21:05:42
|
| I manage a legacy system that dumps it's data into a number of different databases (same schema) on a nightly basis using bulk insert. I need to formulate a strategy for efficiently aggregating that data into a single database right after these nightly extractions complete. Here is my current stategy:1. Duplicate the legacy system's database schema and add an identifier column to specify which database the data loaded from.2. Each night, delete all records in the table.3. Each night, for each database:3a. Set each table's default value to a value that references the current database being loaded. 3b. Use the legacy system's flat files and format files to bulk insert into the database.3c. Clear the default value.What other steps would faciliate performance? Dropping and recreating the indexes? Does anyone forsee faults in this strategy?Thanks,Matt |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-02-01 : 00:01:28
|
| Didn't follow you. You load data and keep them in db for one day(step 2)? Why clean up default value? |
 |
|
|
lotek
Starting Member
42 Posts |
Posted - 2008-02-01 : 11:25:44
|
| Yes, the data is contained in the database for one day. But in the evening it needs to be deleted and repopulated so that it contains the most recent data.I suppose cleaning up the default value is not required, but just to be on the safe side. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-02-01 : 22:52:04
|
| Use 'truncate table', much efficient then delete in this case. |
|
|
|
|
|
|
Bulk Insert and Data Aggregation