SQL Server – Do Multiple Queries in Batch Execute in Parallel

From http://msdn.microsoft.com/en-us/library/ms175502(v=sql.105).aspx

Rules for Using Batches

The following rules apply to using batches:

• CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE SCHEMA, CREATE TRIGGER, and CREATE VIEW statements cannot be combined with other statements in a batch. The CREATE statement must start the batch. All other statements that follow in that batch will be interpreted as part of the definition of the first CREATE statement.
• A table cannot be changed and then the new columns referenced in the same batch.
• If an EXECUTE statement is the first statement in a batch, the EXECUTE keyword is not required. The EXECUTE keyword is required if the EXECUTE statement is not the first statement in the batch.

The Data Loading Performance Guide was written for SQL Server 2008 but as far as I can tell Microsoft hasn't made any improvements in this area for heaps. Here's a quote for your loading scenario:

Bulk Loading an Empty, Nonpartitioned Table

Loading data into a nonpartitioned table, while a simple operation, can be optimized in several ways.

...

Multiple, concurrent insert operations for heaps are possible only when the chosen bulk method issues bulk update (BU) locks on the table. Two bulk update (BU) locks are compatible, and hence two bulk operations can run at the same time.

In this scenario, both INSERT … SELECT and SELECT INTO have a drawback. Both of these operations take an exclusive (X), table level lock on the destination. This means that only one bulk load operation can run at a given time, limiting scalability. However, BCP, BULK INSERT, and Integration Services are all capable of taking bulk update (BU) locks – if you specify the TABLOCK hint.

The important part is that you don't get a BU lock with INSERT ... SELECT. You'll always get an exclusive lock on the table, so only one INSERT can run at a time.

In the comments you said that you'll insert 100k rows or less and that other processes won't be running on the tables during the inserts. When sending two INSERT queries to the database I would expect one of three things to happen:

1. One insert runs first and blocks the other insert. The second insert waits until the first insert is done.
2. One insert finishes before the second insert starts. There is no explicit blocking but they aren't run concurrently.
3. You get a deadlock and only one insert completes successfully.

In all cases you either benefit or aren't hurt by adding a TABLOCKX hint to the query, so that is my recommendation of working around the deadlock. If you want to know why the deadlock sometimes happens you'll need to look to another answer for that.

For in a different scenario in which you really need parallel insert, two ways of working around the BU issue are to partition your heap and to have each session insert into a separate partition or to load your data through BCP, BULK INSERT, or Integration Services.