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