Which one is superior: many inserts OR UNION ALL? SQL Server

Question

Which is better to use:

INSERT #tableN (ID,NAME,Value) SELECT 1, 'a', SUM(a) FROM tab UNION ALL SELECT 2, 'b', SUM(b) FROM tab UNION ALL SELECT 3, 'c', SUM(c) FROM tab UNION ALL SELECT 4, 'd', SUM(d) FROM tab UNION ALL SELECT 5, 'e', SUM(e) FROM tab UNION ALL ... SELECT 3000, 'a3000', SUM(a3001) FROM tab UNION ALL SELECT 3001, 'a3001', SUM(a3001) FROM tab 

or

 INSERT #tableN (ID,NAME,Value) SELECT 1 , 'a', SUM(a) FROM tab INSERT #tableN (ID,NAME,Value) SELECT 2, 'b', SUM(b) FROM tab INSERT #tableN (ID,NAME,Value) SELECT 3, 'c', SUM(c) FROM tab INSERT #tableN (ID,NAME,Value) SELECT 4, 'd', SUM(d) FROM tab ... INSERT #tableN (ID,NAME,Value) SELECT 3001, 'a30001', SUM(a3001) FROM tab 

Answer 1

It seems that you want to rotate the table to create a new table. There are more accurate ways to do this than to write (or generate) all of this SQL. Examine PIVOT and UNPIVOT especially carefully. It allows you to convert columns to rows and provide filtering on an intermediate set of results. You may have to find a few more resources in relation to the rotation, but the link above should start with you well enough if you decide to consider this option.

Answer 2

@Josh has the right answer for your case, but if you are wondering if multiple INSERT or one INSERT with UNION ALL , a single INSERT with UNION ALL is faster when you insert a lot of data.

From the comments of this post :

 I try to execute 400 insert statements to table about 20 columns (int or float). Regarding performance there is MUCH difference. ;) INSERT statements way: about 5 seconds INSERT SELECT UNION ALL way: 40 miliseconds!!!