Have you ever considered what differences between INSERT INTO and SELECT .. INTO ? When do we use each statement appropriately?

In general, both of statements insert data into a table. In INSERT INTO requires you to have a table already created and it should have matching data structure to data source. I means that the table must be existing but SELECT INTO does not require, it helps you to create a table automatically.

SELECT INTO can be only run once time. If you re-run it, the error will be raised because of existing table. You must drop the table before re-running.

If structure of data source or data length changes, there won’t be any error with SELECT INTO but INSERT INTO statement will fail.

INSERT INTO is logged while SELECT INTO is minimally logged. For demonstration, I created a database named Demo, a table Employee with 3 records.

CREATE TABLE [dbo].[Employee](
 [EmployeeID] [int] IDENTITY(1,1) NOT NULL,
 [EmployeeFirstName] [nvarchar](50) NULL,
 [EmployeeLastName] [nvarchar](50) NULL,
 [Salary] [money] NULL
 ) ON [PRIMARY]
GO
 SET IDENTITY_INSERT [dbo].[Employee] ON
GO
 INSERT [dbo].[Employee] ([EmployeeID], [EmployeeFirstName], [EmployeeLastName], [Salary]) VALUES (1, N'Henry', N'William', 1000.0000)

 INSERT [dbo].[Employee] ([EmployeeID], [EmployeeFirstName], [EmployeeLastName], [Salary]) VALUES (2, N'Joe', N'Phil', 2000.0000)

 INSERT [dbo].[Employee] ([EmployeeID], [EmployeeFirstName], [EmployeeLastName], [Salary]) VALUES (3, N'Jack', N'Nielse', 1000.0000)
 GO
 SET IDENTITY_INSERT [dbo].[Employee] OFF

The first exam, created a new table Employee_INSERINTO which has the same structure with Employee table and then insert data into it.

CREATE TABLE [dbo].[Employee_INSERINTO](
 [EmployeeID] [int] IDENTITY(1,1) NOT NULL,
 [EmployeeFirstName] [nvarchar](50) NULL,
 [EmployeeLastName] [nvarchar](50) NULL,
 [Salary] [money] NULL
 ) ON [PRIMARY]

Insert data into Employee_INSERINTO with INSERT INTO statement

INSERT INTO [Employee_INSERINTO]
 SELECT EmployeeFirstName,EmployeeLastName,Salary
 FROM Employee

Now, we observe the data log by using fn_dblog(), total number of log records for INSERT operation is 127. LSN (Log Sequence Number) is start from ‘00000024:00000088:0001’  to ‘00000024:000000d0:0021’

Next, we used SELECT INTO to create and insert data into a table EmployeeSELECTINTO

SELECT EmployeeID, EmployeeFirstName,EmployeeLastName,Salary
 INTO EmployeeSELECTINTO
 FROM Employee

Continue verifying log records, total records for the operation is 65 because LSN is from ‘00000024:000000d8:0001’ to ‘00000024:000000e8:0020’

Conclusions

I personally prefer the INSERT INTO for a permanent table because I want to control data type, indexes,… While I would like SELECT INTO in some cases:

  • I need a temporary for my current process
  • I need backup of a table.
  • Quickly create a table from another with complicated structure. For example, original table has 100 columns.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s