When importing raw files for internal conversion, should I use a secondary database or just isolate them within the database?
I've got between 1.25 and 2GB of CSV files to be imported (and already have most of the process running smooth as butter) so my question is: does it make sense as a "best practice" to use a "secondary" database for the import or just load them into the database that I'm going to be working in?
FinalDatabase -- this is the one I'm wanting to do, unless there's a net negative core.Users core.Addresses core.Emails core.OtherTables staging.UsersImport staging.ContactImport
FinalDatabase core.Users core.Addresses core.Emails core.OtherTables StagingDB dbo.UsersImport dbo.ContactImport
Obviously I'll be migrating from one table to the others via scripts, so it doesn't make much difference what the four-part name is going to be, whether it's just
INSERT INTO core.Users (fieldlist) SELECT fieldlist FROM staging.Users
INSERT INTO core.Users (fieldlist) SELECT fieldlist FROM StagingDB.dbo.Users
The cons I've identified with the first style are the following:
- Increases backup size
- Increases file size (including increasing the file size of the existing database -- autogrowth issues)
- Negates the potential use of multiple spindles (in my case not an option :( )
Some of the pros I've identified with the first style:
- The original import data stays with the database in case of future questions (until someone truncates it, of course)
- It gets backed up with the latter data in case you need to recover something
What would be considered a best practice in this situation and why?