james marriot (9/6/2013)
Im aware of the usual way of getting a multi threaded/process insert by utilizing heaps or partitioned tables and multiple insert statements, but if inserting into a heap or partitioned table, is there any way that sql server query optimizer will make a single insert into a multi insert, assuming the destination fulfills all cryteria to allow bulk insert and the data is evenly distributed across all partitions etc?
No. Inserts are single-threaded. Period.
Having said that, SQL Server 2014 is cracking open the door a bit with multi-threaded SELECT INTOs.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service