Q:

Multiple inserts to temp table followed by single insert to material table OR multiple inserts straight to material table

I've got a query that is structured as follows:
CREATE TempTable
...
INSERT INTO TempTable
...
INSERT INTO TempTable
...
INSERT INTO TempTable
...
INSERT INTO MaterialTable
SELECT *
FROM TempTable

Would it be more efficient to avoid using the temporary table and just do several inserts straight to the material table? i.e.
INSERT INTO MaterialTable
...
INSERT INTO MaterialTable
...
INSERT INTO MaterialTable

A:

The answer depends on the exact structure of the destination table and its indexes, and on the  nature of the processes that access the MaterialTable concurrently with your insertion.

TempTable has no indexes; MaterialTable is likely to have several indexes. Depending on the amount of data being inserted and the number of individual inserts, copying from temp is likely to be faster, because a single multi-row check of each index needs to be performed.
TempTable is invisible to outsiders; MaterialTable is likely to be accessed concurrently with your program. In cases like that, the amount of locking and unlocking would be smaller.
Inserting all rows at once offers a better chance to parallelize the insertion for possibly an even faster insert.

None of that would matter much if MaterialTable is small (a few thousand rows). For larger tables the impact will be more significant, so I would recommend keeping the temp table approach when MaterialTable is expected to get large.

