INSERTING DATA IN ORACLE PARTITION TABLE

[Arsalan]

how to insert data from source table to partition table in oracle 11g.  ???????????


SQL> desc sales
 Name                                      Null?    Type
 ----------------------------------------- -------- -----------------------
 PROD_ID                                   NOT NULL NUMBER
 CUST_ID                                   NOT NULL NUMBER
 TIME_ID                                   NOT NULL DATE
 CHANNEL_ID                                NOT NULL NUMBER
 PROMO_ID                                  NOT NULL NUMBER
 QUANTITY_SOLD                             NOT NULL NUMBER(10,2)
 AMOUNT_SOLD                               NOT NULL NUMBER(10,2)
--------------------

create table sales_part
( prod_id number(23),
cust_id number(23),
time_id date,
channel_id number(23),
promo_id number(23),
quatity_sold number(23),
amount_sold number(23)
)
partition by range (time_id)
(
PARTITION emp_info_p0 VALUES LESS THAN (TO_DATE('01-JAN-1998', 'DD-MON-YYYY')),
PARTITION emp_info_p1 VALUES LESS THAN (TO_DATE('01-FEB-1998', 'DD-MON-YYYY')),
 PARTITION emp_info_p2 VALUES LESS THAN (TO_DATE('01-MAR-1998', 'DD-MON-YYYY')),
PARTITION emp_info_p3 VALUES LESS THAN (TO_DATE('01-APR-1998', 'DD-MON-YYYY')),
 PARTITION emp_info_p4 VALUES LESS THAN (TO_DATE('01-MAY-1998', 'DD-MON-YYYY')));

[ErmanArslansOracleBlog]

USE "CREATE TABLE AS SELECT" (CTAS)

Example:

CREATE TABLE SUN_PART_EMP
PARTITION BY hash(deptno) PARTITIONS 2
AS SELECT * FROM EMP;

[Arsalan]

Thanks for quick reply.

Sir I want to insert data in partition table date wise not all  data
from source table just a few specific date of data.

CREATE TABLE sales_info
 (
 emp_id NUMBER NOT NULL,
 join_date DATE NOT NULL,
 email VARCHAR2(100)
 )
 PARTITION BY RANGE (join_date)
 (
 PARTITION emp_info_p0 VALUES LESS THAN (TO_DATE('01-JAN-2011', 'DD-MON-YYYY')),
PARTITION emp_info_p1 VALUES LESS THAN (TO_DATE('01-JAN-2012', 'DD-MON-YYYY')),
 PARTITION emp_info_p2 VALUES LESS THAN (TO_DATE('01-JAN-2013', 'DD-MON-YYYY')),
PARTITION emp_info_p3 VALUES LESS THAN (TO_DATE('01-JAN-2014', 'DD-MON-YYYY')) ,
 PARTITION emp_info_p4 VALUES LESS THAN (TO_DATE('01-JAN-2015',
'DD-MON-YYYY')));



On 10/3/16, ErmanArslansOracleBlog [via Erman Arslan's Oracle Forum]
<[hidden email]> wrote:

>
>
> USE "CREATE TABLE AS SELECT" (CTAS)
>
> Example:
>
> CREATE TABLE SUN_PART_EMP
> PARTITION BY hash(deptno) PARTITIONS 2
> AS SELECT * FROM EMP;
>
>
>
> _______________________________________________
> If you reply to this email, your message will be added to the discussion
> below:
> http://erman-arslan-s-oracle-forum.2340467.n4.nabble.com/INSERTING-DATA-IN-ORACLE-PARTITION-TABLE-tp1349p1350.html
>
> To unsubscribe from INSERTING DATA IN ORACLE PARTITION TABLE, visit
>

[Arsalan]

Thanks for quick reply.

 Sir I want to insert data in partition table date wise not all  data
 from source table just a few specific date of data.

 CREATE TABLE sales_info
  (
  emp_id NUMBER NOT NULL,
  join_date DATE NOT NULL,
  email VARCHAR2(100)
  )
  PARTITION BY RANGE (join_date)
  (
  PARTITION emp_info_p0 VALUES LESS THAN (TO_DATE('01-JAN-2011', 'DD-MON-YYYY')),
 PARTITION emp_info_p1 VALUES LESS THAN (TO_DATE('01-JAN-2012', 'DD-MON-YYYY')),
  PARTITION emp_info_p2 VALUES LESS THAN (TO_DATE('01-JAN-2013', 'DD-MON-YYYY')),
 PARTITION emp_info_p3 VALUES LESS THAN (TO_DATE('01-JAN-2014', 'DD-MON-YYYY')) ,
  PARTITION emp_info_p4 VALUES LESS THAN (TO_DATE('01-JAN-2015',
 'DD-MON-YYYY')));

[ErmanArslansOracleBlog]

Then use a "where condition" with "CTAS"

so the method is the same as I sent you in my previous update

2016-10-03 10:06 GMT+03:00 Arsala [via Erman Arslan's Oracle Forum] <[hidden email]>:

Thanks for quick reply.

 Sir I want to insert data in partition table date wise not all  data
 from source table just a few specific date of data.

 CREATE TABLE sales_info
  (
  emp_id NUMBER NOT NULL,
  join_date DATE NOT NULL,
  email VARCHAR2(100)
  )
  PARTITION BY RANGE (join_date)
  (
  PARTITION emp_info_p0 VALUES LESS THAN (TO_DATE('01-JAN-2011', 'DD-MON-YYYY')),
 PARTITION emp_info_p1 VALUES LESS THAN (TO_DATE('01-JAN-2012', 'DD-MON-YYYY')),
  PARTITION emp_info_p2 VALUES LESS THAN (TO_DATE('01-JAN-2013', 'DD-MON-YYYY')),
 PARTITION emp_info_p3 VALUES LESS THAN (TO_DATE('01-JAN-2014', 'DD-MON-YYYY')) ,
  PARTITION emp_info_p4 VALUES LESS THAN (TO_DATE('01-JAN-2015',
 'DD-MON-YYYY')));

---

If you reply to this email, your message will be added to the discussion below:

http://erman-arslan-s-oracle-forum.2340467.n4.nabble.com/INSERTING-DATA-IN-ORACLE-PARTITION-TABLE-tp1349p1352.html

To start a new topic under Erman Arslan's Oracle Forum, email [hidden email]
To unsubscribe from Erman Arslan's Oracle Forum, click here.
NAML

--