ansaurus

Question

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

Answer 1

+1 A:

There are two ways of inserting the extra information into the MASTERTABLE.

Use triggers - when an insert occurs in PRODUCTCATEGORIES or REGIONCATEGORIES, an insert trigger fires and checks if the row exists in the MASTERTABLE. If not it is added.
Create a stored procedure to insert data into the PRODUCTCATEGORIES and REGIONCATEGORIES table. The stored procedure is then responsible for checking the MASTERTABLE and inserting if necessary.

The 2nd approach has the advantage that it's obvious to someone else maintaining your code what's going on. Triggers can hide important functionality. Stored procedure are usually preferred for transact SQL for performance reasons.

James Westgate 2010-04-04 09:52:03

Wow - That was fast. Alright, creating a stored procedure seems to be the way to go. However, I am a bit puzzled of how the actual SQL statement should look like, any point in the right direction would be highly appreciated.

Stefan Åstrand 2010-04-04 09:59:25

Answer 2

+2 A:

You can easily build your "master table" in runtime:

SELECT  *
FROM    regiontable
CROSS JOIN
        producttable

It will be more efficient, since, unlike the materialized master table, both tables will probably fit into the cache.

If for some reason you'll need to have it materialized, then just write the triggers on both tables:

INSERT
INTO    mastertable
SELECT  r.code, NEW.code
FROM    regiontable t

on mastertable, and

INSERT
INTO    mastertable
SELECT  NEW.code, p.code
FROM    producttable p

on producttable.

Quassnoi 2010-04-04 09:54:03

Thanks! I need to add values in some additional columns in the mastertable so I don't believe a view would do the trick. However I will try the other statements.

Stefan Åstrand 2010-04-04 10:04:07

I found that ".NEW" is a function that MsSQL don't understand (as opposed to Oracle), therefore I were unable to make this work, however I solved it by declaring a variable and running the SQL in VBA as instructed below.

Stefan Åstrand 2010-04-05 12:04:15

Answer 3

A:

Your master table appears to be a cross join of all potential combinations. Thus when you add a new region, you are having to add all potential products in that region. This is a lot of effort for data which can simply be inferred from all potential regions and all potential categories.

I know you mention that there are additional columns. What do these additional columns contain?

I would typically not keep such a table materialized on a normalized database - unless the additional columns you are alluding to need to be assigned in some special way at creation and then altered in some kind of maintenance - even in that case, a sparse table (i.e. only departures from the defaults) with appropriate defaults can work well.

I do have a similar cross join in one of my systems and it contains about 25m rows and allows us to replace very complex logic over a 2-Dimensional 2500 general ledger accounts x 10000 cost centers space where there are "rows" and "columns" of identical logic, yet "islands" of logic.

Cade Roux 2010-04-04 12:12:01

Hi - perhaps I should explain a little more about my intentions with this particular setup. I need the master table to contain all possible variations because I want to assign a value to each of them manually later. The value represents a sales account that should be used when an order is booked into the system. Let's say we have a customer from the US who buys a BOOK, I would want the user to be able to set up this into the system so the order will be booked into 3030, which is the international sales account.

Stefan Åstrand 2010-04-04 13:09:21

@Stefan: why don't you insert a record only when you want to assign a value?

Quassnoi 2010-04-04 13:55:02

@Quassnoi: Valid question. The system does not allow that one variation is left out. That is if you add a new product category or region category you will need to enter a value for each new variation regardless.

Stefan Åstrand 2010-04-04 14:49:46

@Stefan: I would only populate the account config table rows when you know the sales account (i.e. let it be sparse don't allow NULL) and run exception processes/reports on the CROSS JOIN vs the config table (i.e. where not found), before all posting operations to the sales accounts, etc. The UI to add account configurations would use the same logic.

Cade Roux 2010-04-04 15:19:34

Answer 4

A:

Alright, thank you for your answers and suggestions.

I am working with an access project connected to MsSQL database. I tried to solve this by using a table trigger, but none of the suggestions has done the trick for me. Therefore I decided to solve this on the client side with VBA code instead.

It is probably not the proper way to solve it, but it might be useful to know for anyone reading.

The table structure is the same, but I have made a corresponding form for both the product and region table. On the forms AfterInsert event I have the following code:

Region table:

Private Sub Form_AfterInsert()

Dim varRegion As String
Dim strSQL As String

varRegion = Me![code]
strSQL = "INSERT INTO master([region], [product]) SELECT '" & varRegion & "', & _
          [code] FROM product;"

    DoCmd.RunSQL strSQL

End Sub

Product table:

Private Sub Form_AfterInsert()

Dim varProduct As String
Dim strSQL As String

varProduct = Me![code]
strSQL = "INSERT INTO master([region], [product]) SELECT [code], & _ 
          '" & varProduct & "' FROM region;"

    DoCmd.RunSQL strSQL

End Sub

EDIT: Having research the matter a little bit more, I found that this is the code you need to be using for the trigger if you are using SQL Server, if you don't want to use the client side setup.

Apparently, in SQL Server you need to reference a hidden table called "inserted" when you want to get the values of the inserted row. View this link for more info: Multirow Considerations for DML Triggers

Great!

Product table:

-- Trigger statement
CREATE TRIGGER "name-of-trigger"
ON producttable
FOR INSERT AS

    -- Insert statement
INSERT INTO mastertable ([region],[product])
SELECT regiontable.[code], inserted.[code]
FROM regiontable, inserted;

Region table:

-- Trigger statement
CREATE TRIGGER "name-of-trigger"
ON regiontable
FOR INSERT AS

    -- Insert statement
INSERT INTO mastertable ([product],[region])
SELECT producttable.[code], inserted.[code]
FROM producttable, inserted;

Stefan Åstrand 2010-04-05 11:55:31

ansaurus

tags:

views:

answers:

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

related questions