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

BEGIN





END

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:

INSERT INTO myTable (agency_no, fund_no)
 SELECT A.num, F.num
  FROM Numbers AS A
  JOIN Numbers AS F

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

Do:

INSERT INTO myTable (agency_no, fund_no)
SELECT 700 + Nbr, fund_no
  FROM (
       SELECT 0 UNION SELECT 1 UNION
       SELECT 2 UNION SELECT 3 UNION
       SELECT 4 UNION SELECT 5 UNION
       SELECT 6 UNION SELECT 7 UNION
       SELECT 8 UNION SELECT 9 ) S (Nbr)
 CROSS JOIN (
       SELECT '001' UNION
       SELECT '005' UNION
       SELECT '301' UNION
       SELECT '1112') Vals (fund_no) ;

--
- Anith
( Please reply to newsgroups only )