INSERT 1000 entries into a table with SQL

Is there a way using the INSERT command to insert 1000 entries into a table without manually typing each value.  I want to create a table and INSERT 1000-1999 values as primary keys.  Is there some sort of i++ statement I can put in the query?
szadrogaAsked:
Who is Participating?
 
Patrick MatthewsConnect With a Mentor Commented:
Hello szadroga,

You can avoid that sort of thing by using an identity column.  Failing that...

DECLARE @counter int
SET @counter = 1000

WHILE @counter < 2000 BEGIN
      INSERT INTO SomeTable (ID) VALUES (@counter)
      SET @counter = @counter + 1
END

Regards,

Patrick
0
 
szadrogaAuthor Commented:
Is this done in a stored procedure or can I just use it in query designer?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
u need to paste the query in a new query window and run it
0
 
SharathConnect With a Mentor Data EngineerCommented:
you can also try like this.
Have a look on this article.
http://sqlservernation.com/blogs/howtos/archive/2009/03/07/a-view-of-nmbers.aspx#165
 

create table #temp(id int)
;with cte0 as (select 1 as id union all select 1),        
      cte1 as (select 1 as id from cte0 a, cte0 b),
      cte2 as (select 1 as id from cte1 a, cte1 b),
      cte3 as (select 1 as id from cte2 a, cte2 b),
      cte4 as (select 1 as id from cte2 a, cte3 b),
      nums as (select row_number() over (order by id) as id from cte4)
insert into #temp
select * from nums where id between 1000 and 2000
select * from #temp
drop table #temp

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.