Home » SQL & PL/SQL » SQL & PL/SQL » LOOP INSERT INTO PROCEDURE
LOOP INSERT INTO PROCEDURE [message #237984] Wed, 16 May 2007 04:57 Go to next message
pgorama
Messages: 13
Registered: May 2007
Junior Member
Hello, this is my first thread here.

My problem: I'm using 50 external tables that reads data from 50 different csv files. The tablenames for these external tables is Table1, Table2 Table3 etc.

I now want to insert all of the data from all of the 50 tables info one other, single table. My first thought was to loop the table name in the insert into statement by using a variable in the procedure. Something like;

"
create or replace
PROCEDURE Proc_1
IS
i number(38);
v_sql varchar2(100) := 'INSERT INTO FINAL_TABLE (NAME, FNAME, VAL)
SELECT NAME, FNAME, VAL FROM TABLEi;
BEGIN

FOR i IN 1 .. 50
LOOP
EXECUTE IMMEDIATE (v_sql);
END LOOP;
END;
"

I have alsow tried;
EXECUTE IMMEDIATE (v_sql)+i;
EXECUTE IMMEDIATE (v_sql+i);
EXECUTE IMMEDIATE (v_sql)||i;
FROM TABLE||i;
FROM TABLE%;

and so on..

anyone?
Re: LOOP INSERT INTO PROCEDURE [message #237998 is a reply to message #237984] Wed, 16 May 2007 05:43 Go to previous messageGo to next message
psix666
Messages: 51
Registered: April 2007
Location: Azerbaijan
Member

create or replace PROCEDURE Proc_1
IS
  i number(38);
  v_sql varchar2(100) := 'INSERT INTO FINAL_TABLE (NAME, FNAME, VAL)
SELECT NAME, FNAME, VAL FROM TABLE';
BEGIN

  FOR i IN 1 .. 50 LOOP
    EXECUTE IMMEDIATE (v_sql||to_char(i));
  END LOOP;
END;
Re: LOOP INSERT INTO PROCEDURE [message #238006 is a reply to message #237998] Wed, 16 May 2007 06:00 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I'm wondering why you need 50 external tables when you can create one external table that uses several files:

CREATE DIRECTORY mhedir AS 'C:\test\'
/

CREATE TABLE mhe_external(id  NUMBER
                         ,val VARCHAR2(20)
                         )
ORGANIZATION EXTERNAL
(TYPE oracle_loader
 DEFAULT DIRECTORY mhedir
 ACCESS PARAMETERS
 (
  RECORDS DELIMITED BY newline
  FIELDS TERMINATED BY ";"  OPTIONALLY ENCLOSED BY '"'
  (
   id         INTEGER EXTERNAL(6),
   val        CHAR(20)
  )
 )
 LOCATION ( 'file_one.txt', 'file_two.txt')        
)         
/

SELECT *
FROM   mhe_external
/


DROP DIRECTORY mhedir
/


I've got file_one.txt and file_two.txt in my C:\test folder. It seems to work just fine.

MHE
Re: LOOP INSERT INTO PROCEDURE [message #238450 is a reply to message #237984] Fri, 18 May 2007 02:05 Go to previous message
pgorama
Messages: 13
Registered: May 2007
Junior Member
I have tested your solution psix666 and it works fine.THANKS!

I have also tested the (for me) new idea with one external table for all 50 csv files, and it works great, thanks Maaher!
Previous Topic: PL-SQL With Linux
Next Topic: Calculate second highest salary
Goto Forum:
  


Current Time: Fri Dec 09 13:40:58 CST 2016

Total time taken to generate the page: 0.18307 seconds