I try to insert 1 million rows into empty table on MSSQL 2012 Express. Here my script:
-- set statistics time off drop table t1 create table t1 (id int, a text, b text) go -- #1 - 1,000,000 - 30s -> 45s with ID(number) as ( select 1 as number union all select number + 1 from ID where number < 1000000 + 1 ) insert into t1 select number, 'a_' + cast (number as varchar), 'b_' + cast (number/2 as varchar) from ID option(maxrecursion 0) -- #2 - 1 million rows => ~140,000 rows = 120s (have to cancel query) declare @count int set @count = 0 while @count < 1000000 begin set @count = @count + 1 insert into t1 values(@count, 'a_' + cast (@count as varchar), 'b_' + cast (@count/2 as varchar)) end -- #3 - ~1,300,000 rows - 18s -> 20s with temp as ( SELECT ROW_NUMBER() OVER(ORDER BY a.object_id) as tcount from sys.all_columns a, sys.all_columns b where a.object_id = b.object_id ) insert into t1 select tcount, 'a_' + cast (tcount as varchar), 'b_' + cast (tcount/2 as varchar) from temp go declare @count int set @count = 0 while @count < 3 begin with temp as (select max(id) + 1 as max_id from t1) insert into t1 select max_id, 'a_' + cast (max_id as varchar), 'b_' + cast (max_id/2 as varchar) from t1, temp set @count = @count + 1 end -- #4 -- 1,000,000 = 3s -> 4s (have to drop t1 first) with a(k) as ( select 1 as k union all select k + 1 from a where k < 99 + 1 ) , t2 as ( select row_number() over(order by x.k) as k from a x , a y , a z ) select k as id , 'a_' + cast (k as varchar) as a, 'b_' + cast (k/2 as varchar) as b into t1 from t2
After researching , I found 4 solutions. Are there any better solution (not using copy data from files) ?