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

identity-column, insert, sql-server, sql-server-2008

Solution

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

Problem

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?

Original source