Two table Insert - One 2 Many relationship

[mxtreme]

Hey all,

I am looking to write an insert statement that writes one tuple for each product in the parent table and multiple tuples in the child table for each specific product.

For instance,

Parent Table: Product
Columns: prod_id, prod_desc, prod_origin

Child Table: Product Prices
Columns: prod_id, area_code, unit_price


The origin of the data comes from one table so I need to divide the relevant info from this table into my two tables in my database. Changing the table structure to just one table for all the data is not really an option.

Thanx for any help.

Shaun.

 

[Steve101]

Simple solution here is just to have TWO SQL insert statements, both using the same source table, and different respective destination tables.

Then just run each of the queries one after the other. If referencial integrity is set up, then you will need to run the query which creates the parent records, before running the query which creates the child records.

SQL for the respective queries would look something like this:

INSERT INTO Product(Prod_Id, Prod_desc, Prod_Origin)
SELECT Prod_Id, Prod_desc, Prod_Origin
FROM YourSourceTable;


INSERT INTO Product(Prod_Id, area_code, unit_price)
SELECT Prod_Id, area_code, unit_price
FROM YourSourceTable;

Hope this helps,


Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)