Best way to do simultaneous insert in two tables.

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
Post Reply
pyro
Rank: DBA Sênior
Rank: DBA Sênior
Posts: 323
Joined: Thu, 21 Sep 2006 10:21 am
Location: Barala - TT

Personnel with a Java application need to do the insert in two tables with the following structure:
Table A Without an ID field like PK and Table B has an ID field as fk from table A.
My main question is that the ID number need to be the same for each line of both tables and how this application will run at various points I was in doubt as to the insert to not occur from a line reference the other ID between the tables.
LoadingXp
Rank: Programador Sênior
Rank: Programador Sênior
Posts: 65
Joined: Fri, 30 Mar 2007 11:57 am
Location: SP
Contact:
Att.

Luciano Alvarenga M. Pires
DBA ORACLE CERTIFICADO
http://fulloracle.blogspot.com
------------------------------------
Dinheiro é o combustivel da sociedade industrial. Mas na sociedade da informática o combustivel, o poder, é o conhecimento.

Creates a procedure to do this for you brother !!
puts the outputs for java and it was already ...
Trevisolli
Moderador
Moderador
Posts: 2016
Joined: Wed, 12 Jan 2005 3:25 pm
Location: Araraquara - SP
Abraço,

Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP

Another way, the value is the same for both:

Table 1 -> Sequence with NextVal;
Table 2 -> Sequence with Curral;

I believe that tb kills doubt.

Whatever, send it there.
User avatar
fabricio.silva
Rank: Programador Pleno
Rank: Programador Pleno
Posts: 34
Joined: Mon, 12 Mar 2007 7:07 am
Location: Catanduva/SP
Fabricio M. Silva
Oracle Certified Associate
------------------------------
Catanduva - SP

Hello Pyro,

You can use a multiple insert:

Select all

INSERT ALL 
     INTO sal_history 
     VALUES (empid, hiredate, sal) 
     INTO dept_history 
     VALUES (empid, deptid, hiredate) 
SELECT employee_id empid, hire_date hiredate, salary sal, department_id deptid 
FROM employees 
WHERE employee_id > 40;
Does this help?

ABS.
pyro
Rank: DBA Sênior
Rank: DBA Sênior
Posts: 323
Joined: Thu, 21 Sep 2006 10:21 am
Location: Barala - TT

Personal vlw is now running beauty.
Post Reply
  • Information
  • Who is online

    Users browsing this forum: No registered users and 10 guests