INSERT with a Value List

Use INSERT statements with literal values to add new rows to a table. For example:

 

1 SQL> insert into staging.order_item(batch_name, order_code, item_code, incentive_date, amount) values (‘test_batch’,‘abc123’,’42’,ToDate(‘2015-04-01’), 1000000), (‘test_batch’,‘xyz678’,‘1001’, ToDate(‘2015-04-02’), 66.12);

The status_message ‘ok’ confirms our two rows were added.

 

INSERT with SELECT

Use INSERT with SELECT to insert rows into a target table dynamically.

In our example, we’ll set a couple variables, verify they’re resolving correctly, insert some rows into a table, and verify that our rows were added.

 

1. Set variables.

 

First, we’ll set two variables to calculate the time period:

 

1 SQL> set v_period_end_date = ToDate(‘2015-04-30’);

 

1 SQL> set v_period_name = LookupPeriodName(:v_period_end_date,‘MONTHLY’);

‘

 

2. Check variables.

Use EVAL to retrieve and verify the current value of a variable:

 

1 SQL> eval v_period_name;

 

3. Insert some rows.

This operation uses INSERT with a SELECT statement:

 

1 SQL> insert into staging.order_item(batch_name, order_code, item_code, amount, amount_unit_type_name, incentive_date)

select ‘test_batch’,

employee_id || ‘_’ || first_name,

‘Trigger_’ || :v_period_name,

1,

ut.name,

:v_period_end_date

from xactly.xc_participant ptp

inner join xactly.xc_unit_type ut on ut.name = ‘QUANTITY’

where :v_period_end_date between ptp.effective_start_date and ptp.effective_end_date;

NOTE: SELECT syntax uses a colon to call a variable (:variable_name).

 

 

4. Check your table.

Use SELECT to verify that the items you inserted now appear in the table:

 

1 SQL> select batch_name, order_code, item_code, amount, amount_unit_type_name, incentive_date from staging.order_item;