Can Gurus out there tell me when loading data from one table to another
table on sql server 2000 sp3a through DTS (transform data task), why in some
cases tempdb grows like balloon and in some other cases tempdb doesn't grow
at all.

Here is my situation: I am loading data for few large tables from Server A
to Server B through DTS (transform data task object). All of my destination
tables schema is different from source tables in the sense that quite a few
columns are added at the end. I am testing to load them one table at a time.
For some tables, Looks like sql server is loading data directly to
destination table without using tempdb and for some other tables it loads
data into tempdb first and then loads data into destination tables. Why is
the difference? What dictates wheather to use tempdb or not?

Recovery model is simple on both source and destination db. I am using table
lock option and batch size is 0. I changed batchsize also, but same
behavior.

Thanks