SQL Server inserting huge number of rows to a table with default values and identity column in it

Question

I need to insert about 6400000 rows a table with 2 columns

CREATE TABLE [DBName].[DBO].[BigList] 
(
[All_ID] [int] identity(1,1) NOT NULL, 
[Is_It_Occupied] [int] default(0) not null 
)

I am using the following code today, which takes very long time about 100 minutes.

    SET @NumberOfRecordsToInsert = 6400000;
WHILE (@NumberOfRecordsToInsert > 0)
BEGIN
    INSERT [DBName].[DBO].[BigList] DEFAULT VALUES;
    SET @NumberOfRecordsToInsert = @NumberOfRecordsToInsert - 1
END

Does anyone have a better way to do this?

Mchenry

Is there any index on this table? – Ephedrine

Have you considered to declare Is_It_Occupied as BIT? – Pardue

What about FILL FACTOR of your database? – Pardue

Is the database large enough to hold the table without auto growing during the insertions? You may get a performance increase by pre-allocating enough space to hold the data (and log) for the entire table, before starting the insertion process. If you monitor the server (using perfmon, etc.) while the insertions are happening, you can watch "log grow" and "database grow" events, etc. to see if they are happening too often (and adversely effecting performance). – Wildeyed

Answer 1

Grab a hold of 6400000 rows from somewhere and insert them all at once.

insert into BigList(Is_It_Occupied)
select top(6400000) 0
from sys.all_objects as o1
  cross join sys.all_objects as o2
  cross join sys.all_objects as o3

Did some testing on how long time the different solutions took on my computer.

Solution                                           Seconds
-------------------------------------------------- -----------
Mikael Eriksson                                    13
Naresh                                             832
Dd2                                                25
TToni                                              92
Milica Medic                                       90
marc_s                                             2239

Chad

Answer 2

Your main problem is that each statement runs within a separate transaction. Putting everything in one transaction isn't advisable because very large transactions create their own problems.

But the biggest bottleneck in your code will be the I/O on the transaction log. The following code achieves a 14 MB/s overall write rate on my Laptop (with a Samsung 840 SSD) and runs in 75 seconds:

DECLARE @NumberOfRecordsToInsert INT = 6400000;
DECLARE @Inner INT = 10000;
SET NOCOUNT ON
WHILE (@NumberOfRecordsToInsert > 0)
BEGIN
    BEGIN TRAN
      SET @Inner = 0
      WHILE (@Inner < 10000)
      BEGIN
        INSERT [BigList] DEFAULT VALUES;
        SET @Inner = @Inner+1
      END
    COMMIT
    SET @NumberOfRecordsToInsert = @NumberOfRecordsToInsert - @Inner
END

Ephedrine

Answer 3

Why don't you use this:

INSERT [DBName].[DBO].[BigList] DEFAULT VALUES;
GO 6400000

in SQL Server Management STudio, this will execute the command as many times as you specify in the GO xxxx statement

But even so: inserting over 6 million rows will take some time!

Tovatovar

I typed this in Management studio and its showing me incorrect syntax error – Mchenry

@Naresh: well then: what version of SQL Server and which edition (Express, Web, Standard, etc.) are you using? Did you type this in a new, blank query page? Or did you highlight just those two lines to execute them? It works just fine for me - SQL Server 2008 R2 or 2012, Developer Editions. – Tovatovar

SQL Server 2008. Also, again I have to use that statement inside a stored procedure. So, probably GO is not a best option for me. – Mchenry

@Naresh: it won't work inside a stored procedure, since GO is not a SQL keyword - it's a SQL Server Mgmt Studio specific delimiter. It works just fine if you run it in Mgmt Studio (you never mentioned anything about this code having to execute inside a stored procedure!) – Tovatovar

You have specified DefaultValues with insert command.what defualt values it will take while inserting 6400000 records??? – Sibling

@Learning: those that are defined on that table - you can define default values for each column in your table definition - or if none are specified, SQL Server will use its own defaults (like 0 for an int column etc.) for each column – Tovatovar

@marc_s:Ok got it sir.thanks and this was a pretty good solution.I was searching to insert millions of records in tables and was thinking that i will do this in loop and then found your solution pretty good and work like a charm.Thanks :) – Sibling

Answer 4

You can try something like this, it took me less time than running your query:

 SET NOCOUNT ON 
 BEGIN TRAN 
 DECLARE @i INT 
 SET @i = 1 
 WHILE @i <= 6400000 
 BEGIN 
 INSERT INTO [DBName].[DBO].[BigList] DEFAULT VALUES 
 SET @i = @i + 1 
 END 
 COMMIT TRAN

Hope it helps

Intentional

This works, but takes about 100 minutes. So need something different – Mchenry

Answer 5

DECLARE @NoRows INT
DECLARE @temp AS TABLE (Is_It_Occupied INT)

SET @NoRows = 1000
WHILE (@NoRows > 0)
BEGIN
    INSERT INTO @temp (Is_It_Occupied) VALUES (0)
    SET @NoRows = @NoRows - 1
END

SET @NoRows = 6400
WHILE (@NoRows > 0)
BEGIN
    INSERT INTO BigList (Is_It_Occupied)
    SELECT Is_It_Occupied FROM @temp
    SET @NoRows = @NoRows - 1
END

Pardue