Fastest or most efficient way to insert data in sql server

Sometimes we need to insert large or big amount of data from one table to another table using INSERT SELECT statement. For example:

INSERT INTO tblDestination

SELECT * FROM tblSource

Here we will discuss how to optimize (or write most efficient way) above insert statement:

1. If a table has not any clustered index:

INSERT INTO tblDestination WITH(TABLOCK)

SELECT * FROM tblSource WITH(NOLOCK READUNCOMMITTED)

2. If a table has not any clustered index but has other non-clustered indexes:

DBCC TRACEON (610)

GO

INSERT INTO tblDestination WITH(TABLOCK)

SELECT * FROM tblSource WITH(NOLOCK READUNCOMMITTED)

DBCC TRACEOFF (610)

GO

Note: It is dependent on query execution plan.

3. If a table has only clustered index and destination table is empty:

INSERT INTO tblDestination WITH(TABLOCK)

SELECT * FROM tblSource WITH(NOLOCK READUNCOMMITTED)

Or

DBCC TRACEON (610)

GO

INSERT INTO tblDestination

SELECT * FROM tblSource WITH(NOLOCK READUNCOMMITTED)

DBCC TRACEOFF (610);

GO

Note: the Second option is better if there are multiple insert statements are executing on same tblDestination table.

4. If a table has only clustered index and destination table is not empty:

DBCC TRACEON (610)

GO

INSERT INTO tblDestination

SELECT * FROM tblSource WITH(NOLOCK READUNCOMMITTED)

DBCC TRACEOFF (610);

GO

5. If a table has clustered index as well as non-clustered indexes:

DBCC TRACEON (610)

GO

INSERT INTO tblDestination

SELECT * FROM tblSource WITH(NOLOCK READUNCOMMITTED)

DBCC TRACEOFF (610);

GO

Or

DBCC TRACEON (610)

GO

INSERT INTO tblDestination WITH(TABLOCK)

SELECT * FROM tblSource WITH(NOLOCK READUNCOMMITTED)

DBCC TRACEOFF (610);

GO

Note: It is dependent on query execution plan.Also, the first option is better if there are multiple insert statements are executing on same tblDestination table.

Indexes best practices in SQL server: Where to start creating indexes?
SQL Server query optimization tips: Tuning best practices with examples
Write SQL queries in set-based approach SQL server
SQL server best practices for stored procedure
What is Adhoc query in SQL server: Cached Query Plan