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?

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

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

Answer 3

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

Answer 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!

Answer 5

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