Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- INSERT ALL
- IF NOT EXISTS( SELECT 1 WHERE fo.primary_key='bar' )
- (
- INSERT INTO
- schema.myFoo fo ( primary_key, value1, value2 )
- VALUES
- ('bar','baz','bat')
- ),
- IF NOT EXISTS( SELECT 1 WHERE fo.primary_key='bar1' )
- (
- INSERT INTO
- schema.myFoo fo ( primary_key, value1, value2 )
- VALUES
- ('bar1','baz1','bat1')
- )
- SELECT * FROM schema.myFoo;
- create table t1 (pk int primary key, i int);
- create table t11 (pk int primary key, i int);
- insert into t1 values(1, 1);
- insert into t11 values(2, 21);
- insert into t11 values(3, 31);
- commit;
- MERGE INTO t1 d
- USING t11 s ON (d.pk = s.pk)
- WHEN NOT MATCHED THEN INSERT (d.pk, d.i) VALUES (s.pk, s.i);
- insert /*+ IGNORE_ROW_ON_DUPKEY_INDEX(customer_orders,pk_customer_orders) */
- into customer_orders
- (order_id, customer, product)
- values ( 1234, 9876, 'K598')
- ;
- begin
- insert into customer_orders
- (order_id, customer, product)
- values ( 1234, 9876, 'K698')
- ;
- commit;
- exception
- when DUP_VAL_ON_INDEX
- then ROLLBACK;
- end;
- begin
- select count (*)
- into l_is_matching_row
- from customer_orders
- where order_id = 1234
- ;
- if (l_is_matching_row = 0)
- then
- insert into customer_orders
- (order_id, customer, product)
- values ( 1234, 9876, 'K698')
- ;
- commit;
- end if;
- exception
- when DUP_VAL_ON_INDEX
- then ROLLBACK;
- end;
- insert myFoo
- select *
- from t
- where t.primary_key not in ( select primary_key from myFoo)
- create table t1 (pk int primary key, i int);
- create table t11 (pk int primary key, i int);
- insert into t1 values(1, 1);
- insert into t11 values(2, 21);
- insert into t11 values(3, 31);
- commit;
- MERGE INTO t1 d
- USING t11 s ON (d.pk = s.pk)
- WHEN NOT MATCHED THEN INSERT (d.pk, d.i) VALUES (s.pk, s.i);
- if not exists (...) insert ...
- insert into destination (DESTINATIONABBREV)
- select 'xyz' from dual
- left outer join destination d on d.destinationabbrev = 'xyz'
- where d.destinationid is null;
Add Comment
Please, Sign In to add comment