Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Multiple repeat insert

 
Elizabeth King
Ranch Hand
Posts: 191
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I need to repeat an insert as below

insert into NEW_TABLE (COLUMN_A, COLUMN_B)
select COLUMN_1, COLUMN_2 from OLD_TABLE where COLUMN_3 like 'variable%';

COLUMN_3 is VARCHAR2 type.

I want to loop through the above SQL with a different variable each time.
How do I create a list of the variables and set a for or while loop to
go through it? The list of the variable could be something like: aa, bb, cc, dd, ...
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34420
347
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Elizabeth,
You can add the values to a collection like an ArrayList. Then you can iterate through the list and call the insert each time. Note that it is likely to be more efficient to set a batch insert statement within the loop and then make only one call to the database.
 
Elizabeth King
Ranch Hand
Posts: 191
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I want to do it in a PL/SQL script, not in Java. I'm using Oracle 10g.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34420
347
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Elizabeth,
I didn't catch that from the original post.

Have you looked into PL SQL arrays? Are you stuck on a specific part?
 
Purushoth Thambu
Ranch Hand
Posts: 425
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Since you are in 10g you can make use of regular expression function and get away with loops. Something like below



This will work as long as comma separated input_variable doesn't have spaces between commas.
[ February 27, 2007: Message edited by: Purushothaman Thambu ]
 
Elizabeth King
Ranch Hand
Posts: 191
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Where do I set my string values: aa,bb,cc,dd? Do you mean this:

l_search_value = '^(' || replace(aa,bb,cc,dd,',','%|') || '%)';

insert into NEW_TABLE (COLUMN_A, COLUMN_B)
select COLUMN_1, COLUMN_2 from OLD_TABLE where REGEXP_LIKE(COLUMN_3,l_search_value);
 
Purushoth Thambu
Ranch Hand
Posts: 425
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes, but you forget to give string quotes. It must be

l_search_value = '^(' || replace('aa,bb,cc,dd',',','%|') || '%)';
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic