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 to have another insert statement for the productinstock table for each row that you insert into 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 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).