aspose file tools*
The moose likes JDBC and the fly likes Multiple repeat insert Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Multiple repeat insert " Watch "Multiple repeat insert " New topic
Author

Multiple repeat insert

Elizabeth King
Ranch Hand

Joined: Jul 11, 2002
Posts: 191
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
internet detective
Marshal

Joined: May 26, 2003
Posts: 30294
    
150

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.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Elizabeth King
Ranch Hand

Joined: Jul 11, 2002
Posts: 191
I want to do it in a PL/SQL script, not in Java. I'm using Oracle 10g.
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30294
    
150

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

Joined: May 24, 2003
Posts: 425
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

Joined: Jul 11, 2002
Posts: 191
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

Joined: May 24, 2003
Posts: 425
Yes, but you forget to give string quotes. It must be

l_search_value = '^(' || replace('aa,bb,cc,dd',',','%|') || '%)';
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Multiple repeat insert