Kashif Hussain Kashif Hussain - 4 months ago 10
SQL Question

sql - copy data from another table that include partitions

Im looking to copy data from one table to another, I use Oracle pl/sql, I understand the simple statement

insert into cc_staff_2
select * from cc_staff


This selects all columns from CC_staff and places them in to CC_staff_2. However, both tables have different partitions in them. so for example

I created CC_staff with a partion

CREATE TABLE CC_STAFF
( CCS_STAFF_SK NUMBER (38),
CCS_NAME VARCHAR2 (30),
CCS_BIRTH_DATE DATE,
CCS_GENDER VARCHAR2 (1),
CCS_MAR_STATUS VARCHAR2 (20),
CCS_OFFICE_SK NUMBER (38),
CCS_SALARY NUMBER (38))
PARTITION BY RANGE (CCS_SALARY)
(PARTITION SAL_20 VALUES LESS THAN (20000),
PARTITION SAL_30 VALUES LESS THAN (30000),
PARTITION SAL_50 VALUES LESS THAN (50000))


and then Created Cc_Staff_2 as the same table but with a different partition

CREATE TABLE CC_STAFF
( CCS_STAFF_SK NUMBER (38),
CCS_NAME VARCHAR2 (30),
CCS_BIRTH_DATE DATE,
CCS_GENDER VARCHAR2 (1),
CCS_MAR_STATUS VARCHAR2 (20),
CCS_OFFICE_SK NUMBER (38),
CCS_SALARY NUMBER (38))
PARTITION BY LIST (CCS_OFFICE_SK)
(PARTITION NEWC VALUES (561),
PARTITION LEED VALUES (562),
PARTITION SHEF VALUES (563))


I manually put in data to CC_Staff and now wish to copy this data over to CC_Staff_2. As you can see the columns are the same bar the two last columns with different partitions.

the error message I get is:


ORA-14400 - inserted partition key does not map to any partition

Answer

You have values in the column CCS_OFFICE_SK of the first table different from 561, 562, 563.

You can handle it by creating a partition for every possible value on CCS_OFFICE_SK or by adding a default partition.

Say you have the value 999 in CCS_OFFICE_SK, you would need:

CREATE TABLE Cc_Staff_2
    (           CCS_STAFF_SK        NUMBER (38),
                CCS_NAME            VARCHAR2 (30),
                CCS_BIRTH_DATE      DATE,
                CCS_GENDER          VARCHAR2 (1),
                CCS_MAR_STATUS      VARCHAR2 (20),
                CCS_OFFICE_SK       NUMBER (38),
                CCS_SALARY          NUMBER (38))
    PARTITION BY LIST (CCS_OFFICE_SK)
    (PARTITION  NEWC        VALUES (561),
    PARTITION   LEED        VALUES (562),
    PARTITION   SHEF        VALUES (563),
    PARTITION   p_999       VALUES (999)) 

Or, if you don't want to check all the possible values of CCS_OFFICE_SK, you could use a default partition for values different from 561, 562, 563:

CREATE TABLE Cc_Staff_2
    (           CCS_STAFF_SK        NUMBER (38),
                CCS_NAME            VARCHAR2 (30),
                CCS_BIRTH_DATE      DATE,
                CCS_GENDER          VARCHAR2 (1),
                CCS_MAR_STATUS      VARCHAR2 (20),
                CCS_OFFICE_SK       NUMBER (38),
                CCS_SALARY          NUMBER (38))
    PARTITION BY LIST (CCS_OFFICE_SK)
    (PARTITION  NEWC        VALUES (561),
    PARTITION   LEED        VALUES (562),
    PARTITION   SHEF        VALUES (563),
    PARTITION   P_DEFAULT   VALUES (DEFAULT) )