SQL Server : how would I do a truncate and copy in C#?

Question

I'm starting a new project. The task is to basically move data from a file to a database. Let's say there's one data-file per hour and a line of the file transforms into one row of the database.

Technical Details:

• File-Lines (Entries in DB): 10k - 100k
• Entry: 10 x char[100]

My problem is the updating process.

What I'd like to do is:

Solution 1:

1. Leave the actual datatable in tact (original_table)
2. Move the data from the file to a shadow table (copy_table)
3. When the process is done:
4. Truncate original_table
5. Bulk copy the data from copy_table to original_table

Solution 2:

1. Add new records to the original_table
2. Delete all records with an old timestamp
3. Reorganize original_table (shrink, reset index)

Questions:

1. Which solution is more efficient?
2. Does someone have a better solution for this problem?
3. How would I accomplish step 4/5 from solution 1 and step 3 from solution 2 in C# (with Entity Framework if possible)?

Answer 1

Solution 2 definitely sounds more efficient. Solution 1 sounds needlessly roundabout.

To accomplish steps 4 & 5 from solution 1 or step 3 from solution 2, just use EntityCommand.ExecuteNonQuery with the appropriate T-SQL text - e.g. TRUNCATE TABLE original_table.