BULK INSERT TMP TABLE sql server

It is possible to use (BULK INSERT statement开发者_Python百科) to insert data from a temporary table to another table in the database ?

How can I use this operation since it has a temporary table more than 100,000 records?

Thanks

(sql server 2005)

No, BULK INSERT is only from a file. You could dump the table out to a file and subsequently import it back in using bulk insert.

Is there a reason you can't just insert directly from the temp table into the target table? i.e. INSERT INTO targetTable SELECT * FROM #tempTable

A temp table with only 100,000 records should be inserted into another table in very quickly certainly not long enough to timeout. If you are having timeouts, you have a different problem. First check to see if you have triggers that run a cursor or loop, check to see if your import statement has any correlated subqueries which would cause it to run slowly and check to see if the data is indexed (you can index temp tables but not table varaibles). Look at your execution plan to see where the bottelneck lies.

Another option is to run in batches of 1000 - 10,000 at a time (you may want to experiment to see how big a batch you can get away with.)