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:

https://www.mssqltips.com/sqlservertip/5577/optimize-moving-sql-server-data-from-one-table-to-another-table/

Last modified: March 14, 2019

Author

Comments

Write a Reply or Comment