Inserting Data into Partitioned Tables

hema177 · Post by **hema177** » Fri May 08, 2009 1:23 am

Hi,

I am trying to insert data into a partitioned table which has a partition name based on one of the primary key columns of the table. e.g if the value of the key column is PN GBRTEST the partition name is PN_GBRTEST. So my insert statement should look like

insert into <table_name> partition (<partition_name_from_key>) values (<key>,'DATA1') etc

I am trying to generate the partition name dynamically while inserting using the Oracle Enterprise Stage. Any thoughts on how I will be able to achieve this? So far I've tried using the transformer but I get Insert Prepare Failed ORA-00928: missing SELECT keyword

I would be grateful for any pointers in this issue.

Thanks,

Hema

ray.wurlod · Post by **ray.wurlod** » Fri May 08, 2009 2:36 am

Have you named a "partition table" in your stage properties? You should not need to specify the PARTITION clause in the INSERT statement.

hema177 · Post by **hema177** » Fri May 08, 2009 2:48 am

Hi Ray,

Thanks for the response. I am not sure what you mean. The table on the Database is a partitioned table, and i am trying to insert values in a particular partition by deriving the partition name from a key column. I am able to insert data via Oracle enterprise stage if I hard code the partition name:

INSERT
INTO
#GP_SCHEMA#PS_GP_RSLT_ACUM PARTITION (PN_GBRTEST)
VALUES
(ORCHESTRATE.EMPLID, ORCHESTRATE.CAL_RUN_ID, ORCHESTRATE.EMPL_RCD, ORCHESTRATE.GP_PAYGROUP, ORCHESTRATE.CAL_ID, ORCHESTRATE.ORIG_CAL_RUN_ID, ORCHESTRATE.RSLT_SEG_NUM, ORCHESTRATE.PIN_NUM, ORCHESTRATE.EMPL_RCD_ACUM, ORCHESTRATE.ACM_FROM_DT, ORCHESTRATE.ACM_THRU_DT, ORCHESTRATE.SLICE_BGN_DT, ORCHESTRATE.SEQ_NUM8, ORCHESTRATE.SRC_SYS_ID, ORCHESTRATE.USER_KEY1, ORCHESTRATE.USER_KEY2, ORCHESTRATE.USER_KEY3, ORCHESTRATE.USER_KEY4, ORCHESTRATE.USER_KEY5, ORCHESTRATE.USER_KEY6, ORCHESTRATE.COUNTRY, ORCHESTRATE.ACM_TYPE, ORCHESTRATE.ACM_PRD_OPTN, ORCHESTRATE.CALC_RSLT_VAL, ORCHESTRATE.CALC_VAL, ORCHESTRATE.USER_ADJ_VAL, ORCHESTRATE.PIN_PARENT_NUM, ORCHESTRATE.CORR_RTO_IND, ORCHESTRATE.VALID_IN_SEG_IND, ORCHESTRATE.CALLED_IN_SEG_IND, ORCHESTRATE.SLICE_END_DT, ORCHESTRATE.LOAD_ERROR, ORCHESTRATE.DATA_ORIGIN, ORCHESTRATE.CREATED_EW_DTTM, ORCHESTRATE.LASTUPD_EW_DTTM, ORCHESTRATE.BATCH_SID)

I need to be able to parameterise the partition name based on CAL_RUN_ID so that data can be inserted into different partitions dynamically at runtime.

Hope this clarifies. Sorry if I haven't been able to explain this very well.

Regards,

hema177 · Post by **hema177** » Fri May 08, 2009 4:01 am

Hi Ray,

I see what you mean now, sorry for being so daft. I am not an Oracle person (as must be obvious now.)

Thanks again for your help.

Regards,

Hema