Oracle Insert into Two Tables in One Query

Oracle INSERT into two tables in one query

Try to use from dual;, like this:

INSERT ALL
INTO table1
  (tid, date, title) values (s_tid, s_date, s_title)
INTO table2
  (tid, date, user, note) values (s_tid, s_date, s_user, s_note)
SELECT s_tid, s_date, s_title, s_user, s_note
FROM
( 
    SELECT 
        1 s_tid,
        '01-JAN-15' s_date,
        'title' s_title,
        'john' s_user,
        'test note' s_note
    FROM dual;
)

How to batch insert into two tables with one's id is another's foreign key?

It looks like "unconditional multitable insert" is what you need.

I assumed you get all the values from my_table but you can modify as you need;

INSERT /*+ APPEND */ ALL
INTO USER (ID,NAME,AGE) 
    values (seq_t_user.nextval(), name, age)
INTO ASSET (ID,ID_USER,PRICE) 
    values (seq_t_asset.nextval(), seq_t_user.nextval(), price)
with MY_VALUES as
    (select /*+ parallel */ 
         name,
         age,
         price
     from my_table)
select /*+ parallel */ * from MY_VALUES

edit: We learned that we can't use sequence in multitable insert in the select statement. So we moved the sequence to the insert part.

Oracle 11g Insert Statement into Multiple Tables

Based on your comment, I'm still not clear on what tool you are using to submit the statements to the database.

It's quite possible that your query tool can only handle one statement at a time.

And even if you batch up the 3 statements the way you did, those are still 3 distinct statements, and maybe the tool can't handle that.

A technique that can be used to turn it into a single statement, is by submitting the SQL as part of a single anonymous PL/SQL block. Not sure if your tool will support that. But if it does, you can try this (I left out the select from dual part, as I don't know what the point of it is):

begin
    INSERT INTO report_header ( report_number, company_id, user_id, entry_date) VALUES ( 6797, 15967, 84, TRUNC(SYSDATE));
    INSERT INTO report_detail (part_id, condition_id, uom_id, dvc_id, cqh_id, alt_part_id, entry_date, qty_quoted, qty_req, unit_cost, unit_price, customer_price, route_code) VALUES ((SELECT part_id from parts where pn = '2366'),15,1,3,(select max(report_id) from report_header), (SELECT part_id from parts where pn = '2366'),'11-JUN-2015',1,1,0,1895,1895,'O');
end;

The key is to having the wrapping begin and end; keywords like above. Hopefully it will work for you.

Inserting into two oracle tables with a sequence

You can use a side-effect of the insert all multi-table insert syntax for this:

insert all
into job (jobid, fileid, jobname)
values (jobidsequence.nextval, fileid, jobname)
into reference (jobid, reffileid)
values (jobidsequence.nextval, reffileid)
select  4660 as fileid, 'name' as jobname, 4391 as reffileid
from dual;

2 rows inserted.

select * from job;

     JOBID     FILEID JOBNAME  
---------- ---------- ----------
        42       4660 name       

select * from reference;

     JOBID  REFFILEID
---------- ----------
        42       4391 

SQL Fiddle.

From the restrictions:

You cannot specify a sequence in any part of a multitable insert
statement. A multitable insert is considered a single SQL statement.
Therefore, the first reference to NEXTVAL generates the next number,
and all subsequent references in the statement return the same number.

Clearly I am using a sequence in the values clause, so the first sentence doesn't seem quite accurate; but you can't use it in the select part. (I'm not 100% sure if it can be used in the values in all versions, but the documentation is a little misleading in any case, and contradicts itself).

So I'm taking advantage of the fact that because it is a single statement, the two references to nextval get the same number, as the third sentence says, so the same sequence value is used in both tables.

Oracle sql insert into multiple tables from with clause with restriction

Use conditional insert all like here:

create table a(x, y, z) as (select 0, 0, 0 from dual);
create table b(x, y, z) as (select 0, 0, 0 from dual);
create table c(x, y, z) as (select 0, 0, 0 from dual);

create table src(x, y, z) as (
    select 1, 1, 1 from dual union all
    select 2, 2, 2 from dual union all
    select 3, 3, 3 from dual );

---

insert all 
  when x = 1 then into a (x, y, z) values (x, y, z)
  when x = 2 then into b (x, y, z) values (x, y, z)
  when x = 3 then into c (x, y, z) values (x, y, z)
select * from src

Oracle - PL/SQL Coding - Inserting Into Multiple Tables From Another Table

Using a trigger would be a good way of managing this task. I would place an INSERT or UPDATE conditional trigger on the WAPPLICANT table.

Setting Up Constraints and Auto-Sequenced ID/Key Columns

The values BAID, BAPPID and APPID can be managed through 3 individual Primary Key constraints. If you designed these tables through APEX, you can let Oracle manage the assignment of new index/key values:

1. In the create table wizard, when prompted for PRIMARY KEY, Choose "Populated from a New Sequence".
2. Select the column value you have designated as the Index/Key for the specific table you're creating.
3. You may designate a FOREIGN KEY constraint since there is a connection between the different columns in your tables. It's good practice to make them because it prevents the possibility of orphaned records, like an appointee or a board appointment without an matching application.

If you choose to create a Foreign Key, the order that you create the tables matters... a REFERENCED Primary Key has to exist before a Foreign Key can be created to point to it. If you miss that detail, you can always alter the tables or modify them to add the FK's at a later time.

Setting Up The Trigger

Since The two additional tables are not initially populated by user input, these both can be initialized with their own block of PL/SQL code... in this example, I just stuff it into the trigger... though I advise future caution with putting too much code or logic into triggers... they can kill performance and even lock up other DML operations against the table if the trigger takes to long to finish up.

We will use the trigger to capture the BAPPID value, which you indicate is needed in the other two tables. These are the settings you should designate when creating your trigger through the Apex interface:

1. Build the trigger on table WAPPLICANT
2. Firing Point: AFTER
3. Options: INSERT, UPDATE (I assumed it is possible to insert an application in Accepted status.)
4. When: NEW.BAPPSTATUS = 'Accepted'
5. "For Each Row" (selected)

6. Trigger Body: (see code snippet below. The Apex "Create Trigger" dialogue will fill in most of the customary syntax...)

   INSERT INTO APPOINTEES (
      APPID, 
      APPFNAME,
      APPLNAME,
      APPSUF,
      APPDOB...
   )
   
   VALUES ( :NEW.BAPPID, :NEW.BAPPFNAME, :NEW.BAPPLNAME, :NEW.APPSUF, :NEW.APPDOB ...);
   
   INSERT INTO BRDAPPT (
      BRDID,
      BRDAPT,
      BRDAPPDATE...
    )
   
   VALUES ( :NEW.BAPPBID, :NEW.BAPPID, :NEW.BAPPTBDATE... );
   

A "COMMIT" statement is not necessary within the PL/SQL block of your trigger body. Remember, any Primary Key values that you have associated with a sequence does not need to be included within any of the INSERT commands within your PL/SQL as Apex has actually set up an auto-sequencing trigger for you.

That's it.

How can we insert into multiple tables in oracle using dapper in C#

You can't insert in multiple tables using a single query. But, you can insert in a single connection.

public IDbconnection OpenDbConnection()
{
    var dbconnection = new OracleConnection();
    if(dbconnection.State == ConnectionState.Closed)
    {
        dbconnection.Open();
    }
}
public void BeginTrans()
{
    _db = OpenDbConnection(ConnectionStringHere);
    _tran = _db.BeginTransaction();
}

public void CommitTrans()
{
    _tran.Commit();
    if(_db.State == ConnectionState.Open)
        _db.Close(); // close db connection
}
public void RollbackTrans()
{
    _tran.Rollback();
    if(_db.State == ConnectionState.Open)
        _db.Close(); // close db connection
}

You can write these methods in the same class with an interface (ex IDbInterface, DbClass). You can have two properties injected in the constructor _db and _tran.

You can use these methods as

try
{
   // BeginTrans method 
   Your logic and queries
   // CommitTrans
}
catch
{
   //RollbackTrans
}

---

Related Topics