Some best practices for moving data from one table to another table.
Use TRUNCATE instead of DELETE
If you need to clear the data in a table, use TRUNCATE instead of DELETE. TRUNCATE lock the table instead of at each row. No triggers are activated and no logs are generated resulting in faster performance.
Note: TRUNCATE TABLE will reset your identity to the initial seed, whereas DELETE FROM will carry on incrementing from where it left off.
TRUNCATE requires ALTER privileges on the table.
Change SQL Server Database Recovery Model
It might be possible to reduce the time if we used the Bulk-Logged recovery model. Under the “BULK_LOGGED” recovery, for bulk operations the transactions are not fully logged so this may help in the execution time.
Disable Constraints Before Inserting SQL Server data
If your table has any of these, for optimizing insert operations we can disable these before inserting the data and rebuild the indexes after as well as enabling constraints afterwards.
Use SQL Server SELECT INTO Statement Instead of INSERT INTO Statement
The SELECT INTO will create a new table versus inserting data into a table that has already been setup.
Use TABLOCK hint to boost SQL Server INSERT INTO Performance
When importing data into a heap by using INSERT INTO you can enable optimize logging and locking for the statement by specifying the TABLOCK hint for the target table. By specifying the TABLOCK hint, a shared lock is applied to the entire table instead of at the row or page level.
Sources:
Comments