1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

fill table using different statements for each field

Discussion in 'SQL PL/SQL' started by ecivgamer, May 13, 2011.

  1. ecivgamer

    ecivgamer Active Member

    Messages:
    73
    Likes Received:
    0
    Trophy Points:
    130
    Hi all, I'm about to fill table table1 (field1, field2, etc.) using different statements (and maybe variables) for each field.

    My statements are too different to combine them into one query, but my example is simple:

    statement 1:
    Code (SQL):
     SELECT ttt AS field1 FROM dual
    statement 2:
    Code (SQL):
     SELECT yyy AS field2 FROM dual
    Each statement returns one value. Maybe I need some variable for each value, so var_field1 = field1, var_field2 = field2, etc.

    As a result I need to fill table table1 using my variables, so it must be some procedure.

    Could you please show me some example, how this procedure should be written?
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Why write a procedure? How many of these 'variables' do you have? These could be combined into a single query:

    Code (SQL):
     
    SQL> CREATE TABLE lottacols(
      2          col1 varchar2(10),
      3          col2 varchar2(10),
      4          col3 varchar2(10),
      5          col4 varchar2(10),
      6          col5 varchar2(10),
      7          col6 varchar2(10),
      8          col7 varchar2(10),
      9          col8 varchar2(10),
     10          col9 varchar2(10),
     11          col10 varchar2(10),
     12          col11 varchar2(10),
     13          col12 varchar2(10),
     14          col13 varchar2(10),
     15          col14 varchar2(10),
     16          col15 varchar2(10),
     17          col16 varchar2(10),
     18          col17 varchar2(10),
     19          col18 varchar2(10),
     20          col19 varchar2(10),
     21          col20 varchar2(10),
     22          col21 varchar2(10),
     23          col22 varchar2(10),
     24          col23 varchar2(10),
     25          col24 varchar2(10),
     26          col25 varchar2(10));
    TABLE created.
    SQL>
    SQL>
    SQL> INSERT INTO lottacols
      2  SELECT aaa,bbb,ccc,ddd,eee,fff,ggg,hhh,iii,jjj,kkk,lll,mmm,nnn,ooo,ppp,qqq,rrr,sss,ttt,uuu,vvv,www,xxx,yyy
      3  FROM
      4  (SELECT 'AAA' AS aaa FROM dual),
      5  (SELECT 'BBB' AS bbb FROM dual),
      6  (SELECT 'CCC' AS ccc FROM dual),
      7  (SELECT 'DDD' AS ddd FROM dual),
      8  (SELECT 'EEE' AS eee FROM dual),
      9  (SELECT 'FFF' AS fff FROM dual),
     10  (SELECT 'GGG' AS ggg FROM dual),
     11  (SELECT 'HHH' AS hhh FROM dual),
     12  (SELECT 'III' AS iii FROM dual),
     13  (SELECT 'JJJ' AS jjj FROM dual),
     14  (SELECT 'KKK' AS kkk FROM dual),
     15  (SELECT 'LLL' AS lll FROM dual),
     16  (SELECT 'MMM' AS mmm FROM dual),
     17  (SELECT 'NNN' AS nnn FROM dual),
     18  (SELECT 'OOO' AS ooo FROM dual),
     19  (SELECT 'PPP' AS ppp FROM dual),
     20  (SELECT 'QQQ' AS qqq FROM dual),
     21  (SELECT 'RRR' AS rrr FROM dual),
     22  (SELECT 'SSS' AS sss FROM dual),
     23  (SELECT 'TTT' AS ttt FROM dual),
     24  (SELECT 'UUU' AS uuu FROM dual),
     25  (SELECT 'VVV' AS vvv FROM dual),
     26  (SELECT 'WWW' AS www FROM dual),
     27  (SELECT 'XXX' AS xxx FROM dual),
     28  (SELECT 'YYY' AS yyy FROM dual);
    1 ROW created.
    SQL>
    SQL> commit;
    Commit complete.
    SQL>
    SQL> SELECT * FROM lottacols;
    COL1       COL2       COL3       COL4       COL5       COL6       COL7
    ---------- ---------- ---------- ---------- ---------- ---------- ----------
    COL8       COL9       COL10      COL11      COL12      COL13      COL14
    ---------- ---------- ---------- ---------- ---------- ---------- ----------
    COL15      COL16      COL17      COL18      COL19      COL20      COL21
    ---------- ---------- ---------- ---------- ---------- ---------- ----------
    COL22      COL23      COL24      COL25
    ---------- ---------- ---------- ----------
    AAA        BBB        CCC        DDD        EEE        FFF        GGG
    HHH        III        JJJ        KKK        LLL        MMM        NNN
    OOO        PPP        QQQ        RRR        SSS        TTT        UUU
    VVV        WWW        XXX        YYY

    SQL>