SQL Server can do minimally logged inserts when certain conditions are met. This can improve performance and keep the transaction log from filling up. As stated in the SQL Server documentation , the conditions for doing this with a "insert into ... select" statement are:
The recovery model of the database is set to simple or bulk-logged.
The target table is an empty or non-empty heap.
The target table is not used in replication.
TheTABLOCKhint is specified for the target table.
Is there a way to get Compose to add a WITH (TABLOCK) to the insert statement it generates? This would be particularly useful for loading staging and temporary tables. It could also be used then "optimized for initial load" is selected for the task.
Hi @dpurdy66 , you can manually edit the query via Compose by going to the DWH panel, Press and Hold "CTRL" button, click on "ETL Commands" (while holding "ctrl"), open the query you wish to edit, and click save once the changes are done. Kindly notice that you will be required to do same steps manually each time (for different queries) or whenever you regenerate the task