INSERT 14 mil+ rows with little downtime

[bmpsu]

SQL Server 2005

I need to import two flat files (.txt). Combined, these two files contain over 14 million rows. I need to load these files every weekend which will replace the destination table completely. I need to do this with as little impact on the destination table as possible (as far as locks and/or data-less table). The files are located on the same server the database is located.

I've thought about loading a staging table (no keys or indexes), applying keys and indexes after data is loaded and then renaming the tables so that staging becomes destination. I would be writing a VB.net program to process this.

I wanted to get some thoughts on other ways I can accomplish this with little impact. I could possibly have clients across the world accessing this data at 1 am Saturday morning my time. If more information is needed, let me know. Thanks.

 

[RiverGuy]

One idea would be to give the users access to a view instead of the base table and change the definition of the view after your staging table is loaded.

However, I'm wondering about the 14 million rows. Are there 14 million changes each week, or are you simply getting the entire data set each time? Would it be possible to get only the changed rows and run them through SSIS/DTS?

 

[bmpsu]

Ah! I like the view idea. I will explore.

This load process is a little different from what I am used to. This data is coming from a mainframe computer. I am actually supplied with nightly flat files in which I merge into this table (new and updated rows). However, we receive the weekend file which is the entire data set. And to ensure we have an accurate data set in our sql server, I am to replace the entire table with this new weekend flat file.

 

[bmpsu]

I was reading about partition switching. This is a new term to me, but I have been doing a lot of ready over the last hour. This may/may not be what I need, but wanted to get some inputs. (

http://www.microsoft.com/technet/pr...ice/loading_bulk_data_partitioned_table.mspx)

This seems like a similar scenario. I am having a hard time grasping the concept of partitioned tables. I understand the meaning of the term, but parts of the outline below I don't quite understand how to achieve.

Any thoughts on this outline:
• Create staging table (matches logical structure of target table)
• Load data from flat file into staging table
• Create indexes on staging table
• Execute the ALTER TABLE...SWITCH statement to move data from staging table to (empty partition of target table).

How do I create an empty partition?
Is it possible to switch the target partition with the actual table? (Maybe TRUNCATE target table before transferring the partition?)