This discussion is archived
3 Replies Latest reply: Nov 21, 2012 11:27 AM by rp0428 RSS

Insert data

choti Newbie
Currently Being Moderated
Please help me to insert data into the following table
create table xddw.temp_quarter_vj
(QNum_in_Year varchar2(128)
,Year Number
,QNum varchar2(128)
)

Insert data into table from 2006 to 2030

Expected output for 01.01.2006
QNum_in_Year=012006
year=2006
Qnum=01
  • 1. Re: Insert data
    ranit B Expert
    Currently Being Moderated
    insert into temp_quarter_vj 
    select 
        to_char(to_date('01.01.'||level,'mm.dd.yyyy'),'Q')||level as "Qnum_in_year",
        level "year",
        to_char(to_date('01.01.'||level,'mm.dd.yyyy'),'Q') "Qnum"
    from dual
    where level>=2006
    connect by level <= 2030;
    Edited by: ranit B on Nov 22, 2012 12:54 AM
  • 2. Re: Insert data
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    choti wrote:
    Please help me to insert data into the following table
    create table xddw.temp_quarter_vj
    (QNum_in_Year varchar2(128)
    ,Year Number
    ,QNum varchar2(128)
    )
    That's thinking ahead! With 128 characters for qnum_in_quarter, you'll be all set through the year 999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999. But why do you need 128 characters for the quarter? Given that you do need 128 characters for qnum, shouldn't you have at least 132 characters for qnum_in_year?
    If you don't need that many characters, make the maximum sizes smaller. Making the strings to big is just asking for trouble.
    Insert data into table from 2006 to 2030
    
    Expected output for 01.01.2006
    QNum_in_Year=012006
    year=2006
    Qnum=01
    Here's one way:
    INSERT INTO     temp_quarter_vj (qnum_in_year, year, qnum)
    WITH     quarters   AS
    (
         SELECT     TO_CHAR (LEVEL, 'FM00')     AS qnum
         FROM     dual
         CONNECT BY     LEVEL     <= 4
    )
    ,     years        AS
    (
         SELECT     2005 + LEVEL     AS year
         FROM     dual
         CONNECT BY     LEVEL     <= 25
    )
    SELECT     q.qnum || TO_CHAR (y.year)
    ,     y.year
    ,     q.qnum
    FROM          quarters     q
    CROSS JOIN     years          y
    ;
  • 3. Re: Insert data
    rp0428 Guru
    Currently Being Moderated
    >
    create table xddw.temp_quarter_vj
    (QNum_in_Year varchar2(128)
    ,Year Number
    ,QNum varchar2(128)
    )
    >
    Why are you using a table like this that contains redundant data and has numeric data stored as strings?

    Why not just create a view that gives you the information you need in the format you want?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points