Quick Note About INSERT INTO vs SELECT INTO
Published: Feb 19, 2016
The debate about INSERT INTO vs SELECT INTO can be quite long and get people quite heated and therefore I’m not going to go into the entire thing right now, but what I did want to mention was something that I encountered the other day and which could be worth considering next time you’re deciding which approach to take.

Basically this is in regard to the logging done internally within SQL Server depending on which syntax you use. Although note that this will only really benefit you if you are using real tables in a database and not # tables because everything in tempDB is minimally logged and therefore this makes no difference for temp tables.

Let’s have a look at a couple of examples using the AdventureWorks2012 database.

My database is in SIMPLE recovery and therefore what we’re going to do is to firstly perform a CHECKPOINT to clear the log (which will always leave 3 records), perform a SELECT INTO, and then check the number of log records generated:

use AdventureWorks2012
go

checkpoint
go

select count(*) from fn_dblog(null, null)

select SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty, ProductID,
      
SpecialOfferID, UnitPrice, UnitPriceDiscount, rowguid, ModifiedDate
into dbo.temp
from
sales.SalesOrderDetail

select count(*) from fn_dblog(null, null)

drop table temp
go


Now let’s perform the same thing again, but this time we’ll create the table first and then insert into it:

use AdventureWorks2012
go

checkpoint
go

select count(*) from fn_dblog(null, null)

create table dbo.temp
(
  
SalesOrderID int not null,
  
SalesOrderDetailID int not null,
  
CarrierTrackingNumber nvarchar(25) null,
  
OrderQty smallint not null,
  
ProductID int not null,
  
SpecialOfferID int not null,
  
UnitPrice money not null,
  
UnitPriceDiscount money not null,
  
LineTotal  AS (isnull((UnitPrice*((1.0)-UnitPriceDiscount))*OrderQty,(0.0))),
  
rowguid uniqueidentifier not null,
  
ModifiedDate datetime not null,
  
constraint PK_temp primary key clustered (SalesOrderID, SalesOrderDetailID)
)
insert into dbo.temp
select
SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty, ProductID,
      
SpecialOfferID, UnitPrice, UnitPriceDiscount, rowguid, ModifiedDate
from sales.SalesOrderDetail

select count(*) from fn_dblog(null, null)

drop table temp
go


As you can see, the results are impressive. The difference between INSERT INTO and SELECT INTO are vast. This is because SELECT INTO is a minimally logged operation whereas INSERT INTO is fully logged.

Noting that this is a relatively small table you can imagine how much this will hammer your log file (not to mention potential file growth lag) if you did this with a large table.

Something to consider next time you’re coding this type of thing.

Again though, as mentioned above, this is irrelevant when using # tables as tempDB is always minimally logged and therefore this applies to full tables only.

Just in case you don’t believe me:

use tempDB
go

checkpoint
go

select count(*) from fn_dblog(null, null)

select SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty, ProductID,
      
SpecialOfferID, UnitPrice, UnitPriceDiscount, rowguid, ModifiedDate
into #temp
from AdventureWorks2012.sales.SalesOrderDetail

select count(*) from fn_dblog(null, null)

drop table #temp
go


use tempDB
go

checkpoint
go

select count(*) from fn_dblog(null, null)

create table #temp
(
  
SalesOrderID int not null,
  
SalesOrderDetailID int not null,
  
CarrierTrackingNumber nvarchar(25) null,
  
OrderQty smallint not null,
  
ProductID int not null,
  
SpecialOfferID int not null,
  
UnitPrice money not null,
  
UnitPriceDiscount money not null,
  
LineTotal  AS (isnull((UnitPrice*((1.0)-UnitPriceDiscount))*OrderQty,(0.0))),
  
rowguid uniqueidentifier not null,
  
ModifiedDate datetime not null,
  
constraint PK_temp primary key clustered (SalesOrderID, SalesOrderDetailID)
)
insert into #temp
select SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty, ProductID,
      
SpecialOfferID, UnitPrice, UnitPriceDiscount, rowguid, ModifiedDate
from AdventureWorks2012.sales.SalesOrderDetail

select count(*) from fn_dblog(null, null)

drop table #temp
go


Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London

Categories


Archives


Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron