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?

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). — dav1dsm1th, Dec 01 '13 at 20:26

score 8 · Answer 1 · answered Dec 01 '13 at 15:52

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

Mikael Eriksson · Accepted Answer · 2013-12-02T07:46:43.223

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

score 4 · Answer 3 · answered Dec 01 '13 at 12:37

4

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!

answered Dec 01 '13 at 12:37

marc_s

732,580
175
1,330
1,459

I typed this in Management studio and its showing me incorrect syntax error – Naresh Dec 01 '13 at 12:43
2

@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. – marc_s Dec 01 '13 at 12:47
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. – Naresh Dec 01 '13 at 12:51
1

@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!) – marc_s Dec 01 '13 at 17:39
You have specified DefaultValues with insert command.what defualt values it will take while inserting 6400000 records??? – I Love Stackoverflow Jan 25 '17 at 08:07
1

@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 – marc_s Jan 25 '17 at 10:37
@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 :) – I Love Stackoverflow Jan 25 '17 at 11:20

score 1 · Answer 4 · answered Dec 01 '13 at 13:27

1

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

answered Dec 01 '13 at 13:27

Milica Medic Kiralj

3,580
31
31

This works, but takes about 100 minutes. So need something different – Naresh Dec 01 '13 at 14:03

bjnr · Answer 5 · 2013-12-01T18:42:55.923

1

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

edited Dec 01 '13 at 18:42

answered Dec 01 '13 at 16:43

bjnr

3,353
1
18
32

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

5 Answers5