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).
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?