0 votes
1 view
in SQL by (20.3k points)

I'm looking for a good way to perform multi-row inserts into an Oracle 9 database. The following works in MySQL but doesn't seem to be supported in Oracle.

INSERT INTO TMP_DIM_EXCH_RT 

(EXCH_WH_KEY, 

 EXCH_NAT_KEY, 

 EXCH_DATE, EXCH_RATE, 

 FROM_CURCY_CD, 

 TO_CURCY_CD, 

 EXCH_EFF_DATE, 

 EXCH_EFF_END_DATE, 

 EXCH_LAST_UPDATED_DATE) 

VALUES

    (1, 1, '28-AUG-2008', 109.49, 'USD', 'JPY', '28-AUG-2008', '28-AUG-2008', '28-AUG-2008'),

    (2, 1, '28-AUG-2008', .54, 'USD', 'GBP', '28-AUG-2008', '28-AUG-2008', '28-AUG-2008'),

    (3, 1, '28-AUG-2008', 1.05, 'USD', 'CAD', '28-AUG-2008', '28-AUG-2008', '28-AUG-2008'),

    (4, 1, '28-AUG-2008', .68, 'USD', 'EUR', '28-AUG-2008', '28-AUG-2008', '28-AUG-2008'),

    (5, 1, '28-AUG-2008', 1.16, 'USD', 'AUD', '28-AUG-2008', '28-AUG-2008', '28-AUG-2008'),

    (6, 1, '28-AUG-2008', 7.81, 'USD', 'HKD', '28-AUG-2008', '28-AUG-2008', '28-AUG-2008');

1 Answer

0 votes
by (40.3k points)

Use the below code, it works well in Oracle:

insert into pager (PAG_ID,PAG_PARENT,PAG_NAME,PAG_ACTIVE)

select 8000,0,'Multi 8000',1 from dual

union all select 8001,0,'Multi 8001',1 from dual

Note: One thing to remember is to use the from dual statement.

Related questions

0 votes
1 answer
asked Jul 29, 2019 in SQL by Tech4ever (20.3k points)
0 votes
1 answer
0 votes
1 answer
0 votes
1 answer
Welcome to Intellipaat Community. Get your technical queries answered by top developers !


Categories

...