For Loop in MSSQL

Posted on 2014-08-02
Last Modified: 2014-08-03
Hi Experts,

Is there a quick way that I can insert records in a MSSQL, using a for loop?


For I = 156 to 200

   INSERT INTO INVOICES (fldComment) Values ('abc');

Next I

Here, I'm trying to insert 44 identical records, for later use.

Any help appreciated

Thank you.
Question by:APD_Toronto
    LVL 23

    Accepted Solution

    Just us a WHILE loop, since it is functionally equivalent (there is no FOR loop in MSSQL)
    DECLARE @start INT = 156;
    DECLARE @end INT = 200;
    DECLARE @loopvar INT;
    SET @loopvar = @start;
    WHILE (@loopvar <= @END)
        INSERT INTO INVOICES (fldComment) VALUES ('abc');
        SET @loopvar = @loopvar + 1;

    Open in new window

    LVL 12

    Expert Comment

    by:Harish Varghese
    It is good to keep a table that stores a good number of serial numbers (say 1 to 30000) which will make the job easy when you want to generate statements like these. You can use below statement to create and generate such a table:
    Create Table TableOfNumbers (Value int Primary Key)
    ;with cte as
    (Select 1 as Num
    	Union All
    	Select num + 1
    	From cte
    	Where Num < 30000
    Insert into TableofNumbers
    Select * from cte 
    option (maxrecursion 0)

    Open in new window

    And you can achieve the same result with a single SELECT statement as below:
    INSERT INTO INVOICES (fldComment) 
    SELECT 'abc'
    FROM TableOfNumbers T
    WHERE T.Value <= 44

    Open in new window

    LVL 10

    Expert Comment

    Here is the similar code for you.

    declare @cnt int
    set @cnt=1
    WHILE @cnt<=40
       exit when @cnt>40
       insert into ...
       set @cnt=@cnt+1

    Open in new window

    LVL 68

    Expert Comment

    Thru SSMS (or any interface that supports the "GO" command), there's a very easy way:

    INSERT INTO INVOICES (fldComment) Values ('abc');
    GO 44

    Author Closing Comment

    That was easy :)
    LVL 23

    Expert Comment

    I must say, I do like ScottPletcher's solution.  I almost never use the numeric argument to GO and often forget it's there.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    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.

    Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…

    759 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

    14 Experts available now in Live!

    Get 1:1 Help Now