Bulk Insert and Data Aggregation

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
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200801/1Hi
You could add a view with your extra "column" or use a query to export from
each database and then you don't have to worry about the defaults as the
value is in the flat files. This would mean that you could load in parrallel
although having separate tables for each database may allow you to reduce
contention when doing this. If you had separate tables then you could use a
partitioned view to "join" them all, this view could identify the source if
necessary.
John
"lotek via SQLMonster.com" <u16539@.uwe> wrote in message
news:7f0a31b9b1f67@.uwe...
>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
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200801/1
>