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

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