Insert dymamic values in sql

Question

I would like to insert a new row into database Oracle.

INSERT INTO xx_vms.es_supplier_process_steps (process_step_id, supplier_process_id, supplier_step_id,
mapped_item_type, mapped_subinventory_code, mapped_subinventory_type, mapped_operation_code, mapping_notes)
VALUES (xx_vms.seq_es_supplier_proc_step_id.NEXTVAL, xx_vms.seq_es_supplier_proc_id.CURRVAL,
xx_vms.seq_es_supplier_step_id.CURRVAL, 'ASY', null, 'IN', null, null);

With xx_vms.seq_es_supplier_proc_id.CURRVAL column is result of this below query. Example: 122:

select supplier_process_id from xx_vms.es_supplier_processes where supplier_code='TEST'and name='FINAL TEST';

But I don't know how to do this. Please help me! Thanks.

I would like to replace xx_vms.seq_es_supplier_proc_id.CURRVAL because it's not true. The exact value for this column is the result of 2nd query. — R.Kaka, May 07 '13 at 04:32

hrezs · Answer 1 · 2013-05-07T04:39:08.960

0

you will want to use a subquery for the insert

 INSERT INTO xx_vms.es_supplier_process_steps 
      (process_step_id, 
      supplier_process_id, 
      supplier_step_id,
      mapped_item_type, 
      mapped_subinventory_code, 
      mapped_subinventory_type, 
      mapped_operation_code, 
      mapping_notes)
 SELECT x_vms.seq_es_supplier_proc_step_id.NEXTVAL, 
       (select supplier_process_id 
         from xx_vms.es_supplier_processes 
         where supplier_code='TEST'and 
               name='FINAL TEST'),
        xx_vms.seq_es_supplier_step_id.CURRVAL, 
        'ASY', 
        null, 
        'IN', 
        null, 
        null
 FROM DUAL);

edited May 07 '13 at 04:39

answered May 07 '13 at 03:56

hrezs

772
1
8
22

Sorry, I can't understand this query. – R.Kaka May 07 '13 at 04:36
I took your query, formatted it, turned it into a select insert (example: http://stackoverflow.com/questions/25969/sql-insert-into-values-select-from) and replaced what you needed to be a query with your query. – hrezs May 07 '13 at 04:40
Don't you think 'FROM DUAL' is needed for the SELECT statement? Also, semicolon at the end of the inner sub query is not correct. – Noel May 07 '13 at 04:40
@Noel funny, I added the FROM DUAL as you posted that. Removed the semicolon =) – hrezs May 07 '13 at 04:42

Insert dymamic values in sql

1 Answers1