asked on

Oracle - Insert into table issue

Hello all

I have never created an SQL to insert into table but only to query.

I would like to know how to build that SQL to insert into table based on the query folowwing that column sequence:
D.HOL_NAME,
A.PICK_DC AS DC,
A.STORE_NUM,
A.XD_THRESHOLD,
C.LANE_NUM,
C.PICK_DC,
C.RECEIVE_DC,
C.SHIP_DAY AS RELEASE_DAY,
C.ARRIVE_DAY,
C.RECEIVE_ARRIVE_OPEN,
C.RECEIVE_ARRIVE_CLOSE,
C.REC_CREATE_ID,
TO_CHAR(C.REC_CREATE_TMS,'yyyy-mm-dd HH24:MI:SS'),
C.REC_CHNG_ID,
TO_CHAR(C.REC_CHNG_TMS,'yyyy-mm-dd HH24:MI:SS'),
TO_CHAR(D.EFFECTIVE_START_TMS,'yyyy-mm-dd HH24:MI:SS'),
TO_CHAR(D.EFFECTIVE_END_TMS,'yyyy-mm-dd HH24:MI:SS')

Thanks again for your help.

SQL to query i use:

SELECT 
    D.HOL_NAME, 
    A.PICK_DC AS DC, 
    A.STORE_NUM, 
    A.XD_THRESHOLD, 
    C.LANE_NUM, 
    C.PICK_DC, 
    C.RECEIVE_DC, 
    C.SHIP_DAY AS RELEASE_DAY, 
    C.ARRIVE_DAY, 
    C.RECEIVE_ARRIVE_OPEN, 
    C.RECEIVE_ARRIVE_CLOSE, 
    C.REC_CREATE_ID, 
    TO_CHAR(C.REC_CREATE_TMS,'yyyy-mm-dd HH24:MI:SS'), 
    C.REC_CHNG_ID, 
    TO_CHAR(C.REC_CHNG_TMS,'yyyy-mm-dd HH24:MI:SS'), 
    TO_CHAR(D.EFFECTIVE_START_TMS,'yyyy-mm-dd HH24:MI:SS'),
    TO_CHAR(D.EFFECTIVE_END_TMS,'yyyy-mm-dd HH24:MI:SS')
    
FROM 
    LCLRPT.DC_STORE_SETTINGS A, 
    LCLRPT.LCL_STORE_LANE_REF B, 
    LCLRPT.XD_LANES C, LCLRPT.
    LCL_HOLIDAY_LANES D

WHERE 
    A.DC_STORE_SETTINGS_SEQ_NUM= B.DC_STORE_SETTINGS_SEQ_NUM 
    AND B.LANE_NUM= C.LANE_NUM
    AND B.LANE_GROUP=D.SEASONAL_LANE_NUM
    AND A.DC_STORE_SETTINGS_SEQ_NUM = D.DC_STORE_SETTINGS_SEQ_NUM
    AND HOL_NAME LIKE 'CANADA%2013%'
    GROUP BY D.HOL_NAME, 
    A.PICK_DC, 
    A.STORE_NUM, 
    A.XD_THRESHOLD, 
    C.LANE_NUM, 
    B.STORE_LANE_REF_SEQ_NUM, 
    C.PICK_DC, 
    C.RECEIVE_DC, 
    C.SHIP_DAY, 
    C.ARRIVE_DAY, 
    C.RECEIVE_ARRIVE_OPEN, 
    C.RECEIVE_ARRIVE_CLOSE, 
    C.REC_CREATE_ID, 
    TO_CHAR(C.REC_CREATE_TMS,'yyyy-mm-dd HH24:MI:SS'), 
    C.REC_CHNG_ID, 
    TO_CHAR(C.REC_CHNG_TMS,'yyyy-mm-dd HH24:MI:SS'), 
    TO_CHAR(D.EFFECTIVE_START_TMS,'yyyy-mm-dd HH24:MI:SS'),
    TO_CHAR(D.EFFECTIVE_END_TMS,'yyyy-mm-dd HH24:MI:SS')
    ORDER BY D.HOL_NAME, 
    A.PICK_DC, 
    A.STORE_NUM, 
    B.STORE_LANE_REF_SEQ_NUM;

Open in new window

Steve Wales

If the table exists and matches (exactly) the column layout of your select statement it's as simple as:

insert into YourTableName
select (and the rest of your query goes here).

If you want to create the table and you have create table privileges you can go

create table YourTableName as
select (and the rest of your query goes here).

If the table exists and doesn't exactly match the column order of you select statement you can go:

insert into YourTableName (columnname1, columnname2, columnname3 ....)
select (and the rest of your query goes here)

In that instance, you'd need one column name for each column in your query.

Wilder1626

ASKER

Hi,

Yes, the tables exists and matches (exactly) the column layout.

How do i insert if all the information are in an excel spreadsheet?

ASKER CERTIFIED SOLUTION

Steve Wales

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Wilder1626

ASKER

Thanks for your help