I have a query that returns 14 million results that I need to store in a table, the SELECT query itself returns in a little over 2 minutes but INSERT statement takes well over 30 minutes (which is unacceptable). We are running this in an Azure Database currently set as a Premium tier database, and I need to keep this process all contained within SQL (no exporting to file etc).
Sample Query:
SELECT
v.Param1,
v.Param2,
lt.Param3,
lt.Param4
INTO #tempTable
FROM <View> v
join LargeTable lt
on lt.nVarCharCol = v.nVarCharCol
INSERT INTO DestTable SELECT Para1, Param2, Param3, Param4 FROM #tempTable
Things I have tried so far:
- Turned off indexes on DestTable
- Batching inserts
- Creating indexes on temp tables
What is the fastest/best way to insert these query records into a table?
Any recommendations to speed this process up?