SQL Server: Basic performance test between SELECT INTO and INSERT INTO SELECT

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

In this post, I am doing a basic performance test between SELECT INTO and INSERT INTO SELECT option of SQL Server.

Database Developers are using these options very frequently for copying data between the tables. But SELECT INTO is little faster than INSERT INTO SELECT option.

Note: I am testing using a very small number of sample records. If you are testing with a huge number of records, you may find a big difference in the performance.

Please check the below examples:

Set Statistics:

1 2	SET STATISTICS IO ON SET STATISTICS TIME ON

Create sample tables:

1 2 3 4 5

CREATE TABLE tbl_MyData (ID INT, Name VARCHAR(10)) CREATE TABLE tbl_Mytable (ID INT, Name VARCHAR(10))   INSERT INTO tbl_MyData VALUES (1,'dbrnd') GO 1000000

Perform INSERT INTO SELECT:

1 2 3

INSERT INTO tbl_Mytable SELECT ID, Name FROM tbl_MyData

Execution time:

1 2	SQL Server Execution Times: CPU time = 16 ms, elapsed time = 37 ms.

Perform SELECT INTO:

1 2 3

SELECT ID, Name INTO tbl_MyTable2 FROM tbl_MyData

Execution time:

1 2	SQL Server Execution Times: CPU time = 16 ms, elapsed time = 21 ms.