Declare @SelectSql nvarchar(max), @InsertSql nvarchar(max); SELECT @SelectSql = COALESCE(@SelectSql + ' UNION ALL ', '') + 'Select '''+employeename+''' As employeename, datesold, itemsold, COUNT(itemsold) As [totalsales] ' +'FROM '+employeename+' ' FROM storeinfo; -- print @SelectSql; -- Exec(@inlinesql); SET @InsertSql = 'Insert into holdingtable ' + @SelectSql; print @InsertSql; exec (@InsertSql);
Not sure wheather it will help with performance, but surely is more elegant than cursor.
Edit (as requested by Andriy):
If it takes so long, consider aggregating data more often, I mean divide the work to smaller chunks. For example you can every hour take only the data that was added during this time, aggregate, add to the table. This would require more work. Maybe you will need include some table for pre-aggregations -> aggregate every hour to HourlyHoldingTable, then every nigth aggregate this much smaller table to the DailyHodlingTable. Or only add a comumn with a number of rows aggregated so far, to enable correct update. Many solutions possible here. The general idea is to split the big task to more smaller tasks.
Or you could have an indexed view with aggregation for each table.
The problem is, we don't have enough information - the query you pasted is not complete (missing group by), so it is hard to tell wether problem is in grouping huge number of rows, or in inserting loads of rows.