It would be helpful if you provided a profile or query plan - it's hard to provide accurate advice otherwise.
My guess based on the limited information is that you are inserting into a large number of partitions and that the query is running out of memory because it is buffering all those partitions in memory at once.
It could also easily be concurrent queries or a low memory limit for the query, but I am making an educated guess based on past experience of what I've seen other people run into.
There would be two ways to solve that:
* Reduce the number of partitions that you're inserting into with a single query (change the table's partitioning, break up the insert into multiple queries, each for a subset of partitions).
* Add a CLUSTERED hint to the insert so that it sorts the data and then inserts into a partition at a time. This reduces the memory requirements drastically.
