[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

INSERT 1000 entries into a table with SQL

Posted on 2009-05-01
4
Medium Priority
?
361 Views
Last Modified: 2012-05-06
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?
0
Comment
Question by:szadroga
4 Comments
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 1000 total points
ID: 24282055
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
 

Author Comment

by:szadroga
ID: 24282198
Is this done in a stored procedure or can I just use it in query designer?
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24282284
u need to paste the query in a new query window and run it
0
 
LVL 41

Assisted Solution

by:Sharath
Sharath earned 1000 total points
ID: 24284779
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question