Professional Web Applications Themes

Looping through a list and inserting into a table - Microsoft SQL / MS SQL Server

Hi, I've got two lists and I want to loop through them and insert their values into a table. My first list, lstAgency_no is for the first field, and consists of 10 agency numbers (700 through 709), and the second list, lstFund_No is for the second field, and consists of a list of four funds (not incremental in nature): 001, 005, 301, 1112. I'm just having a hard time with the syntax as a relative newbie :) What I basically wish to avoid doing is the following: INSERT INTO myTable (agency_no, fund_no) VALUES (700, 001) INSERT INTO myTable (agency_no, fund_no) ...

  1. #1

    Default Looping through a list and inserting into a table

    Hi,

    I've got two lists and I want to loop through them and insert their
    values into a table. My first list, lstAgency_no is for the first
    field, and consists of 10 agency numbers (700 through 709), and the
    second list, lstFund_No is for the second field, and consists of a
    list of four funds (not incremental in nature): 001, 005, 301, 1112.

    I'm just having a hard time with the syntax as a relative newbie :)

    What I basically wish to avoid doing is the following:

    INSERT INTO myTable (agency_no, fund_no) VALUES (700, 001)
    INSERT INTO myTable (agency_no, fund_no) VALUES (700, 005)
    INSERT INTO myTable (agency_no, fund_no) VALUES (700, 301)
    INSERT INTO myTable (agency_no, fund_no) VALUES (700, 1112)
    ....
    ....
    ....
    INSERT INTO myTable (agency_no, fund_no) VALUES (709, 001)
    INSERT INTO myTable (agency_no, fund_no) VALUES (709, 005)
    INSERT INTO myTable (agency_no, fund_no) VALUES (709, 301)
    INSERT INTO myTable (agency_no, fund_no) VALUES (709, 1112)

    Any help is greatly appreciated!

    Sincerely,
    Andrew
    Andrew Guest

  2. #2

    Default Re: Looping through a list and inserting into a table

    DECLARE i INT
    SET i = 700
    WHILE i <= 709
    BEGIN
    INSERT myTable(agency_no, fund_no) VALUES(i, 001)
    INSERT myTable(agency_no, fund_no) VALUES(i, 005)
    INSERT myTable(agency_no, fund_no) VALUES(i, 301)
    INSERT myTable(agency_no, fund_no) VALUES(i, 1112)
    SET i = i + 1
    END



    "Andrew Peterson" <com> wrote in message
    news:google.com... 


    Aaron Guest

  3. #3

    Default Re: Looping through a list and inserting into a table

    I assume your lists are something like a comma-separated string?

    You could create a table of numbers from 1 to n (where n is at least as
    large as the largest number you'll need to INSERT).

    Then do:

    DECLARE lstAgency_no VARCHAR(100), lstFund_No VARCHAR(100)

    SET lstAgency_no = '700,701,702,703,704,705,706,707,708,709'
    SET lstFund_No = '1,5,301,1112'

    INSERT INTO myTable (agency_no, fund_no)
    SELECT A.num, F.num
    FROM Numbers AS A
    JOIN Numbers AS F
    ON ','+lstAgency_no+',' LIKE '%,'+CAST(A.num AS VARCHAR)+',%'
    AND ','+lstFund_No+',' LIKE '%,'+CAST(F.num AS VARCHAR)+',%'

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Guest

Similar Threads

  1. Looping over a list...
    By AppDeveloper in forum Coldfusion Database Access
    Replies: 13
    Last Post: October 16th, 12:13 PM
  2. Inserting List Values in Table
    By DDhillon in forum Macromedia ColdFusion
    Replies: 17
    Last Post: July 5th, 03:57 AM
  3. parsing and looping over a list
    By Balance in forum Macromedia ColdFusion
    Replies: 8
    Last Post: June 22nd, 02:22 PM
  4. looping over a list problem
    By jmercmon in forum Coldfusion - Advanced Techniques
    Replies: 4
    Last Post: April 8th, 10:52 PM
  5. [PHP] Looping through a list - Newbie question
    By Javier Tacon in forum PHP Development
    Replies: 2
    Last Post: August 27th, 03:45 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139