INSERT 1000 entries into a table with SQL

Posted on 2009-05-01
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?
Question by:szadroga
    LVL 92

    Accepted Solution

    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



    Author Comment

    Is this done in a stored procedure or can I just use it in query designer?
    LVL 75

    Expert Comment

    by:Aneesh Retnakaran
    u need to paste the query in a new query window and run it
    LVL 40

    Assisted Solution

    you can also try like this.
    Have a look on this article.

    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


    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

    731 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now