10777 read from one table TO two tables (save)

Hello experts:

I have a table with 6 columns, the first 2 columns (SalesOrderID, SalesOrderNumber) must be save in OrderHeader table. The following 4 columns must be save (ProductID, OrderQty, UnitPrice, LineTotal) in OrderDetail table, both columns have the same SalesOrderID.

Please support.
enrique_aeoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Please provide the table definitions.
0
slightwv (䄆 Netminder) Commented:
insert into orderheader (select SalesOrderID, SalesOrderNumber from some_table);
insert into OrderDetail (select ProductID, OrderQty, UnitPrice, LineTotal from some_table);


Are you looking to do this real-time on insert into the 6 column table?

Maybe an insert trigger?
0
slightwv (䄆 Netminder) Commented:
Example trigger code (typed in, untested):

create or replace trigger tab_trig
after insert on some_table
for each row
begin
insert into orderheader values( :new.SalesOrderID, :new.SalesOrderNumber);
insert into OrderDetail values(:new.ProductID, :new.OrderQty, :new.UnitPrice, :new.LineTotal);
end;
/
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

enrique_aeoAuthor Commented:
The information's going to send me a excel file, what I will do is pass it on to a table and then from there to the final tables.

This is the table's
-- 1. OrderHeader
create table TA_VENTA_PRODUCTO_CENTRO
(
  IDVENTAPRODUCTOCENTRO NUMBER(8) not null,
  IDPRODUCTO            NUMBER(8),
  IDCENTRO              NUMBER(8) not null,
  IDCLIENTE             NUMBER(8) not null,
  IDDIRECCIONZONAVENTA  NUMBER(8) not null,
  OBSERVACION           VARCHAR2(200),
  USUARIOCREACION       VARCHAR2(20) default USER not null,
  FECHACREACION         DATE,
  USUARIOEDICION        VARCHAR2(20),
  FECHAEDICION          DATE
)
-- Create/Recreate primary, unique and foreign key constraints
alter table TA_VENTA_PRODUCTO_CENTRO
  add primary key (IDVENTAPRODUCTOCENTRO)
  using index
  tablespace TBS_APPDAT
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
alter table TA_VENTA_PRODUCTO_CENTRO
  add constraint UNQ_TA_VENTA_PRODUCTO_CENTRO unique (IDPRODUCTO, IDCENTRO, IDCLIENTE)
  using index
  tablespace TBS_APPDAT
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
alter table TA_VENTA_PRODUCTO_CENTRO
  add constraint FK_TA_VTA_PROD_CENTRO_TA_CENT foreign key (IDCENTRO)
  references TA_CENTRO (IDCENTRO);
alter table TA_VENTA_PRODUCTO_CENTRO
  add constraint FK_TA_VTA_PROD_CENTRO_TA_CLI foreign key (IDCLIENTE)
  references TA_CLIENTE (IDCLIENTE);
alter table TA_VENTA_PRODUCTO_CENTRO
  add constraint FK_TA_VTA_PROD_CENTRO_TA_DIR foreign key (IDDIRECCIONZONAVENTA)
  references TA_DIRECCION_ZONA_VENTA (IDDIRECCIONZONAVENTA);
alter table TA_VENTA_PRODUCTO_CENTRO
  add constraint FK_TA_VTA_PROD_CENTRO_TA_PROD foreign key (IDPRODUCTO)
  references TA_PRODUCTO (IDPRODUCTO);
 
 --2. OrderDetail
create table TA_VENTA_MES_ANIO
(
  IDVENTAMESANIO        NUMBER not null,
  IDVENTAPRODUCTOCENTRO NUMBER not null,
  IDANIOEJERCICIO       NUMBER not null,
  IDMES                 NUMBER not null,
  MILLARES              NUMBER,
  COSTOFIJO             NUMBER,
  RESINA                NUMBER,
  TOTAL                 NUMBER,
  USUARIOCREACION       VARCHAR2(20) default USER not null,
  FECHACREACION         DATE,
  FECHAEDICION          DATE
)

-- Create/Recreate primary, unique and foreign key constraints
alter table TA_VENTA_MES_ANIO
  add primary key (IDVENTAMESANIO)
  using index
  tablespace TBS_APPDAT
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
alter table TA_VENTA_MES_ANIO
  add constraint UNQ_TA_VENTA_MES_ANIO unique (IDVENTAPRODUCTOCENTRO, IDANIOEJERCICIO, IDMES)
  using index
  tablespace TBS_APPDAT
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
alter table TA_VENTA_MES_ANIO
  add constraint FK_TA_VTA_MES_ANIO_TA_ANIO foreign key (IDANIOEJERCICIO)
  references TA_ANIO_EJERCICIO (IDANIOEJERCICIO);
alter table TA_VENTA_MES_ANIO
  add constraint FK_TA_VTA_MES_ANIO_TA_MES foreign key (IDMES)
  references TA_MES (IDMES);
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
And what is it exactly you are trying to do? Please give an example (dígame en español if necessary).
0
enrique_aeoAuthor Commented:
Este es una ejemplo con otros datos (table stage)
SalesOrderID       SalesOrderNumber      ProductID      OrderQty      UnitPrice      LineTotal
43659                  SO43659                        776                              1      2024.994      2024.994000
43659                  SO43659                        777                              3      2024.994      6074.982000
43660                  SO43660                        762                              1      419.4589      419.458900
43660                  SO43660                        758                              1      874.794            874.794000

Tablas finales:
Tabla 1
43659      SO43659
43660      SO43660

Tabla 2
43659  776 1      2024.994      2024.994000
43659  777 3      2024.994      6074.982000
43660  762 1      419.4589      419.458900
43660  758 1      874.794            874.794000
0
slightwv (䄆 Netminder) Commented:
Have you not looked at what I posted?
0
slightwv (䄆 Netminder) Commented:
Save the Excel file to a CSV, then use SQL*Loader.

Here is a simple example loading one row into two tables.

It loads 'a' and 'b' into tab1 and 'a','c','d' into tab2.

I think this is sort of what you are wanting.

Just remove begindata and everything after it and use INFILE 'your_csv_file' in place of INFILE *.

Then from a cmd prompt:
sqlldr username/password control=my_control_file.ctl


my_control_file.ctl contents:
LOAD DATA
infile *
INTO TABLE tab1 append
FIELDS TERMINATED BY ","
TRAILING NULLCOLS
(
col1 position(1),
col2
)
INTO TABLE tab2 append
FIELDS TERMINATED BY ","
TRAILING NULLCOLS
(
col1 position(1),
junk1 filler,
col2,
col3
)
begindata
a,b,c,d,e,f

Open in new window

0
enrique_aeoAuthor Commented:
i can not use  SQL*Loader.

I should be only use PL/SQL (cursor maybe?)
0
slightwv (䄆 Netminder) Commented:
What are you wanting to do with PL/SQL?

If you want to upload an Excel file and parse it using PL/SQL, you are out of luck.  There may be some 3rd party tools you can buy that will allow PL/SQL to parse a binary Excel file but I don't think native PL/SQL can.

Anyway, this question was how to get data from a staging table into two other tables.  I've provided two ways of doing it:
Two insert statements
or
A trigger on inserting into the staging table.

What else is needed for this question?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
enrique_aeoAuthor Commented:
Thanks, I'll try and tell them
0
slightwv (䄆 Netminder) Commented:
To add:
If you do find a product to read the native Excel file, why would you need the staging table?

Just parse the Excel file and insert the rows in the correct tables.
0
enrique_aeoAuthor Commented:
do you mean the INSERT in the same excel?
He did not do it, if you have any article or video you send me Please.
0
slightwv (䄆 Netminder) Commented:
As asked this question was how to insert from a staging table into two tables.

You then brought up an Excel spreadsheet.  Any discussion about how to get data from Excel into Oracle needs to be a new question.

What I was talking about is:
If you can parse Excel to get the data into some staging table then parse the staging table, do you need to staging table at all?
0
enrique_aeoAuthor Commented:
El valor de esta columna IDVENTAPRODUCTOCENTRO debe ser el mismo para ambas tablas (TA_VENTA_PRODUCTO_CENTRO y TA_VENTA_MES)

Como genero los valores para esa columna?, actualmente ambas tablas tienen un triggers

CREATE OR REPLACE TRIGGER TGR_VENTA_PRODUCTO_CENTRO
BEFORE INSERT ON TA_VENTA_PRODUCTO_CENTRO
FOR EACH ROW
BEGIN
    SELECT SEQ_idVentaProductoCentro.Nextval into :new.idVentaProductoCentro FROM DUAL;
END;

CREATE OR REPLACE TRIGGER TGR_VENTA_MES_INSERT
BEFORE INSERT ON TA_VENTA_MES
FOR EACH ROW
BEGIN
    SELECT SEQ_idVentaMes.Nextval into :new.idVentaMes FROM DUAL;
END;
0
slightwv (䄆 Netminder) Commented:
If Google translate worked properly:

The value of this IDVENTAPRODUCTOCENTRO column must be the same for both tables (TA_VENTA_PRODUCTO_CENTRO and TA_VENTA_MES)

  As gender values ​​for that column ?, currently both tables have triggers

This appears to be a new question not related to this one.  It should be asked as a new question.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.