Looping through a list and inserting into a table 
Author Message
 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



Fri, 06 Jan 2006 18:44:32 GMT
 Looping through a list and inserting into a table




BEGIN





END


Quote:
> 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



Fri, 06 Jan 2006 18:58:55 GMT
 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:




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
--



Fri, 06 Jan 2006 19:07:35 GMT
 Looping through a list and inserting into a table
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 )



Fri, 06 Jan 2006 19:28:28 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. loop the query throgh list of tables

2. loop thru table multiple selects/inserts

3. SQL to list tables in insert order

4. parsing a comma delimited list to use as a value list in an insert

5. Insert value list does not match column list.

6. inserted table is not inserted to the original table

7. Looping through table to add records to other table

8. How to loop on a delimited list?

9. Looping over Comma-delimited List

10. Looping over a delmited list?

11. SQL insert loop help requested

12. Insert record in a loop


 
Powered by phpBB® Forum Software