How to insert existing data to another table in Oracle

by Aliyev M.H
(Baku)



Hi
I have two tables in my Oracle database:

CREATE TABLE product (
product_id INT,
product_name VARCHAR2(25) NOT NULL,
product_price NUMBER(4,2) NOT NULL,
quantity_on_hand NUMBER(5,0) NOT NULL,
last_stock_date DATE,
constraint pro_pk primary key (product_id));

CREATE TABLE productinstock (
product_id INT,
product_name VARCHAR2(25) NOT NULL,
product_sprice NUMBER(4,2) NOT NULL,
quantity_in_stock NUMBER(5,0) NOT NULL
);

INSERT INTO product
VALUES (5, 'chocolate', 5.00, 10, '15.10.2010');
INSERT INTO product
VALUES (6, 'juice', 11.00, 5, '10.10.2010');

I want product.product_id added to productinstock.product_id when I insert into product table.

thanks in advance

There are two ways to this:
  1. the obvious way is for the application to have an insert statement for the productinstock table as well ass the product table:

    INSERT INTO product
    VALUES (5, 'chocolate', 5.00, 10, '15.10.2010');

    INSERT INTO productinstock(product_id) VALUES (5);

    INSERT INTO product
    VALUES (6, 'juice', 11.00, 5, '10.10.2010');

    INSERT INTO productinstock(product_id) VALUES (6);

  2. the other way is to create a PL/SQL trigger on the product table in your dataabase such that every time a row is inserted into it a row is also inserted into the productinstock table. The trigger would look something like this:

    CREATE OR REPLACE TRIGGER add_to_emp3
    AFTER INSERT ON emp2 FOR EACH ROW
    BEGIN
        INSERT INTO productinstock(product_id)
    VALUES (:NEW.prodcut_id);
    END;
That answers the how but it doesn't answer the bigger question - why.

Why would you have 2 tables with very similar columns in your database and why would you just insert the product_id into the 2nd table?

It's unusual for there to be a one to one relationship between two entities in your database - usually one is a child of the other and is constrained via the use of foreign key constraints. As it stands there is nothing to prevent additional rows being inserted into the productinstock table and nothing to prevent the two tables from becoming out of sync in your database. You could for example delete a product from one but not the other and you could insert a product into productinstock that is not in product.

Issues such as those need to be considered as part of the application and Oracle database design and usually the application would insert data into the tables in the database and constraints would provide additional protection against application errors (as they work at the database level).

You would probably find our Oracle SQL tutorials and our Oracle PL/SQL tutorials worthwhile.

If you're interested in formal training (either on-line or in a classroom) then see our Oracle training page for more information.

Click here to post comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.