Unfortunately, there aren't a lot of great design articles out there. It seems strange, but there are books that go very deep and talk relational theory, but not a lot...

I'd run a trace and look for what's doing this. I've never heard of SQL doing this for no reason.

Perhaps security policy is set too low for locking? Or you...

The big thing is do you know the format of the file ahead of time or not?

I've always been wary of automatic merging, worried that changes in logic might not trickle down well. However the manual review probably negates most of my concerns.

My guess is that...

You have issues in your IO subsystem. I'd have the vendor check it out.

And you need to be sure you have the same space available. Regardless of if you have 1MB of data in there, you'd still need to the 650GB of data...

What do you mean server account? A login?

You could do this, but the XP would need to connect with appropriate rights.

I'd go prod->staging (Serverb) -> archive.

I do this so that if there are issues in data quality or a need to cleanse, you can handle things in there. SSIS can...

This isn't an exact science and your data sizes will change constantly. You don't want to be updating space every day or every week.

Set enough space in the data...

You can select a table and script out everything for that table. Right click the table and select Generate Script.

There isn't a lot of difference between temp tables and table variables. They'll both spill over into tempdb if the data sizes are large.

If you have a lot of inserts/updates,...

http://www.sqlservercentral.com/articles/SQL+Server+2005/64547/

select count (x), count(y), count(x) + count(y) 'sum'

from mytable

A query can cause CPU to run to 100% easily. It's not abnormal to have CPU jump to 100% for short periods of time.

If it lasts longer than 5 minutes,...

Please don't cross post.

Answered here: http://www.sqlservercentral.com/Forums/Topic616635-360-1.aspx