Solved

10777 read from one table TO two tables (save)

Posted on 2014-10-06
16
198 Views
Last Modified: 2014-10-07
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.
0
Comment
Question by:enrique_aeo
  • 8
  • 6
  • 2
16 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40363955
Please provide the table definitions.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40363957
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40363960
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
 

Author Comment

by:enrique_aeo
ID: 40363974
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
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40363987
And what is it exactly you are trying to do? Please give an example (dígame en español if necessary).
0
 

Author Comment

by:enrique_aeo
ID: 40364005
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40364010
Have you not looked at what I posted?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40364038
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:enrique_aeo
ID: 40364341
i can not use  SQL*Loader.

I should be only use PL/SQL (cursor maybe?)
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 40364373
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
 

Author Comment

by:enrique_aeo
ID: 40364390
Thanks, I'll try and tell them
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40364426
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
 

Author Comment

by:enrique_aeo
ID: 40364448
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40364474
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
 

Author Comment

by:enrique_aeo
ID: 40366065
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40366081
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now