SQL: How do I INSERT primary key values from two tables INTO a master table.

Posted by Stefan on Stack Overflow See other posts from Stack Overflow or by Stefan
Published on 2010-04-04T09:43:49Z Indexed on 2010/04/04 9:53 UTC
Read the original article Hit count: 279

Filed under:

sql

|

insert

|

relational-database

Hello,

I would appreciate some help with an SQL statement I really can't get my head around.

What I want to do is fairly simple, I need to take the values from two different tables and copy them into an master table when a new row is inserted into one of the two tables.

The problem is perhaps best explained like this:

I have three tables, productcategories, regioncategories and mastertable.

         ---------------------------
TABLE:   PRODUCTCATEGORIES
         ---------------------------
COLUMNS: CODE       | DESCRIPTION
         ---------------------------
VALUES:  BOOKS      | Books
         ---------------------------

         ---------------------------
TABLE:   REGIONCATEGORIES
         ---------------------------
COLUMNS: CODE       | DESCRIPTION
         ---------------------------
VALUES:  EU         | European Union
         ---------------------------

         ---------------------------
TABLE:   MASTERTABLE
         ---------------------------
COLUMNS: REGION     | PRODUCT
         ---------------------------
VALUES:  EU         | BOOKS
         ---------------------------

I want the values to be inserted like this when a new row is created in either productcategories or regioncategories.

New row is created.

         ---------------------------
TABLE:   PRODUCTCATEGORIES
         ---------------------------
COLUMNS: CODE       | DESCRIPTION
         ---------------------------
VALUES:  BOOKS      | Books
         ---------------------------
VALUES:  DVD        | DVDs
         ---------------------------

And a SQL statement copies the new values into the mastertable.

         ---------------------------
TABLE:   MASTERTABLE
         ---------------------------
COLUMNS: REGION     | PRODUCT
         ---------------------------
VALUES:  EU         | BOOKS
         ---------------------------
VALUES:  EU         | DVD
         ---------------------------

The same goes if a row is created in the regioncategories.

New row.

         ---------------------------
TABLE:   REGIONCATEGORIES
         ---------------------------
COLUMNS: CODE       | DESCRIPTION
         ---------------------------
VALUES:  EU         | European Union
         ---------------------------
VALUES:  US         | United States
         ---------------------------

Copied to the mastertable.

         ---------------------------
TABLE:   MASTERTABLE
         ---------------------------
COLUMNS: REGION     | PRODUCT
         ---------------------------
VALUES:  EU         | BOOKS
         ---------------------------
VALUES:  EU         | DVD
         ---------------------------
VALUES:  US         | BOOKS
         ---------------------------
VALUES:  US         | DVD
         ---------------------------

I hope it makes sense.

Thanks,

Stefan

Developer IT