Insert Multiple Records in Oracle

Best way to do multi-row insert in Oracle?

This works 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

The thing to remember here is to use the from dual statement.

Inserting multiple records from a List Person into Oracle DB?

Using plain JDBC you could do something like this:

Connection con = ... //get it from somewhere

//prepare the statement
PreparedStatement stmt = con .prepareStatement("INSERT INTO persons_table (AGE, NAME, ADDRESS, WEIGHT, HEIGHT) VALUES (?, ?, ?, ?, ?)");

//add a batch for each person
for( Person person : personsList ) {
  stmt.setInt(1, person.getAge());
  stmt.setString(2, person.getName());
  stmt.setString(3, person.getAddress());
  stmt.setDouble(4, person.getWeight());
  stmt.setDouble(5, person.getHeight());
  stmt.addBatch();
}

//execute the batch and get results for each query in the order they have been added
int[] results = stmt.executeBatch();

Edit:

As per request, here's how you could filter the persons that got inserted. Note that you'd still need to read the documentation of status codes that your driver might return especially in special cases but let's assume we get 1 for a successfully inserted person and -3 (see PreparedStatement.EXECUTE_FAILED) for a failed insert:

List<Person> insertedPersons = new ArrayList<>();

for( int i = 0; i < results.length; i++ ) {
  int status = results[i];

  if( status == 1 ) { //or any other status that represents success
    insertedPersons.add(personsList.get(i));
  }
}

Edit: executeBatch() might actually throw a BatchUpdateExeption if one of the statements fails. This also exception contains an array of results in the same order as the statements but depending on the driver execution might stop at the first exception and the array might just contain the status up to the exception point.

The approach above would still work but you might change the call like this:

int[] results;
try { 
  results = stmt.executeBatch();
} catch(BatchUpdateException e) { 
  results = e.getUpdateCounts();
}

//in some cases the exception might return null if execution failed right away so initialize to an empty array to prevent a NPE
if( results == null ) {
  results = new int[0];
}

Oracle SQL - how to insert multiple records

One way is using connect by clause

insert into p_assumptions (p_number, value) 
select 11, level || ')'
from dual
Connect by level <= 150;

SQL Multiple Insert into multiple rows

I don't think that Oracle supports VALUES with multiple records. Here is a simple alternative:

INSERT INTO MY_EMPLOYEE
    SELECT 126,'Popov', 'Olga', 'opopov', 8500 FROM DUAL UNION ALL
    SELECT 127, 'Chen', 'Ling', 'lcheng', 14500 FROM DUAL UNION ALL 
    SELECT 128, 'Dunn', 'David', 'ddunn', NULL FROM DUAL;

Note: I do highly recommend that you specify the columns for the insert, but that is another issue.

How to insert / update multiple records in oracle using cursor in stored procedure?

Error you got is due to OPEN P_NGTSHFTALL_CUR; - you don't open ref cursor here. Appart from that, is ref cursor really IN OUT? You aren't returning anything; should be just IN.

As of your code: merge might substitute separate INSERT and UPDATE (and you don't need IF-THEN-ELSE in that case.

Something like this:

CREATE OR REPLACE PROCEDURE INSERT_UPDATE_NIGHTSHIFALLOWANCES_RECORDS (
   P_NGTSHFTALL_CUR   IN     SYS_REFCURSOR,
   SPRESULT              OUT VARCHAR2,
   SPRESPONSECODE        OUT VARCHAR2,
   SPRESPONSEMESSAGE     OUT VARCHAR2)
AS
   id             nightshiftallowances.id%TYPE;
   requestnumber  nightshiftallowances.requestnumber%TYPE;
   shifttype      nightshiftallowances.shifttype%TYPE;
   startdate      nightshiftallowances.startdate%TYPE;
   enddate        nightshiftallowances.enddate%TYPE;
   shifttime      nightshiftallowances.shifttime%TYPE;
BEGIN
   LOOP
      FETCH P_NGTSHFTALL_CUR
         INTO id,
              requestnumber,
              shifttype,
              startdate,
              enddate,
              shifttime;

      EXIT WHEN P_NGTSHFTALL_CUR%NOTFOUND;

      MERGE INTO NIGHTSHIFTALLOWANCES a
           USING (SELECT id,
                         requestnumber,
                         shifttype,
                         startdate,
                         enddate,
                         shifttime
                    FROM DUAL) b
              ON (a.id = b.id)
      WHEN MATCHED
      THEN
         UPDATE SET a.requestnumber = b.requestnumber,
                    a.shifttype = b.shifttype,
                    a.startdate = b.startdate,
                    a.enddate = b.enddte,
                    a.shifttime = b.shifttime
      WHEN NOT MATCHED
      THEN
         INSERT     (REQUESTNUMBER,
                     SHIFTTYPE,
                     STARTDATE,
                     ENDDATE,
                     SHIFTTIME)
             VALUES (b.REQUESTNUMBER,
                     b.SHIFTTYPE,
                     b.STARTDATE,
                     b.ENDDATE,
                     b.SHIFTTIME);
   END LOOP;

   COMMIT;
   SPRESULT := 'OK';
   SPRESPONSECODE := 'INSUPDNGTSHFTALL-001';
   SPRESPONSEMESSAGE :=
      'NIGHT SHIFT ALLOWANCE RECORD INSER/UPDATE IS SUCCESSFUL';
EXCEPTION
   WHEN OTHERS
   THEN
      SPRESULT := 'NOK';
      SPRESPONSECODE := 'INSUPDNGTSHFTALL-002';
      SPRESPONSEMESSAGE := SQLERRM;
END INSERT_UPDATE_NIGHTSHIFALLOWANCES_RECORDS;

How to insert multiple object rows into a table in oracle sql?

how do i insert the second row of "kids" (Leanardo, Trudeau, 10).

You cannot. The kids type is defined as:

CREATE TYPE kids AS OBJECT(firstK VARCHAR(20), lastK VARCHAR(20), age INT);

and then you define the table as:

CREATE TABLE person (
  id VARCHAR(10),
  firstName VARCHAR(20),
  lastName VARCHAR(20),
  adr address,
  pn phoneNum,
  ks kids
);

kids (despite the plural in its name) is a singular object so you can only put a single kids value into it.

If you want to put multiple kids then you either need to:

• change the table to store a collection of kids;
• have a separate table for children; or
• reverse the relationship and store two (or more) foreign key relationships for parents.

For example, you could (since children are people too) store the relationship as:

CREATE TYPE address AS OBJECT(
  num    INT,
  street VARCHAR2(20), 
  city   VARCHAR2(20),
  zip INT
);
CREATE TYPE phoneNum AS VARRAY(2) OF VARCHAR2(13);

CREATE TABLE person (
  id            INTEGER
                CONSTRAINT person__id__pk PRIMARY KEY,
  firstName     VARCHAR2(20),
  lastName      VARCHAR2(20),
  date_of_birth DATE,
  adr           address,
  pn            phoneNum
);

CREATE TABLE children(
  id        INTEGER
            CONSTRAINT children__id__pk PRIMARY KEY,
  parent_id CONSTRAINT children__parent_id__fk REFERENCES person (id),
  child_id  CONSTRAINT children__child_id__fk  REFERENCES person (id),
  CONSTRAINT children__pid_cid__u UNIQUE (parent_id, child_id)
);

db<>fiddle here

---

Related Topics