SQL：如何将两个表中的主键值插入主表

Question

我希望能得到一些关于 SQL 语句的帮助，我真的无法理解。

我想要做的非常简单，当新行插入两个表之一时，我需要从两个不同的表中获取值并将它们复制到主表中。

这个问题也许最好这样解释：

我有三个表，产品类别、区域类别和主表。

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

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

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

我希望在产品类别或区域类别中创建新行时像这样插入值。

新行已创建。

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

SQL 语句将新值复制到主表中。

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

如果在区域类别中创建行，情况也是如此。

新行。

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

复制到主表。

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

我希望这是有道理的。

谢谢，

斯特凡

Answer 1

有两种方法可以将额外信息插入到主表中。

1. 使用触发器 - 当 PRODUCTCATEGORIES 或 REGIONCATEGORIES 中发生插入时，将触发插入触发器并检查该行是否存在于 MASTERTABLE 中。如果没有，则添加。

2. 创建一个存储过程以将数据插入到 PRODUCTCATEGORIES 和 REGIONCATEGORIES 表中。然后，存储过程负责检查 MASTERTABLE 并在必要时进行插入。

第二种方法的优点是，对于维护代码的其他人来说，正在发生的事情是显而易见的。触发器可以隐藏重要的功能。出于性能原因，存储过程通常是事务 SQL 的首选。

Answer 2

您的主表似乎是所有潜在组合的交叉联接。因此，当您添加新区域时，您必须添加该区域中的所有潜在产品。对于可以简单地从所有潜在区域和所有潜在类别推断出的数据来说，这是一项巨大的工作。

我知道您提到还有其他列。这些附加列包含什么？

我通常不会在规范化数据库上保留这样的表 - 除非您提到的附加列需要在创建时以某种特殊方式分配，然后在某种维护中进行更改 - 即使在这种情况下，稀疏表（即只有偏离默认值）和适当的默认值才能很好地工作。

我的一个系统中确实有一个类似的交叉联接，它包含大约 2500 万行，允许我们在二维 2500 个总账科目 x 10000 个成本中心空间（其中有“行”和“列”）上替换非常复杂的逻辑具有相同的逻辑，但却是逻辑的“孤岛”。

Answer 3

好的，谢谢您的回答和建议。

我正在处理一个连接到 MsSQL 数据库的访问项目。我尝试通过使用表触发器来解决这个问题，但没有一个建议对我有用。因此我决定在客户端使用 VBA 代码来解决这个问题。

这可能不是解决该问题的正确方法，但了解它可能对任何阅读的人都有用。

表结构是一样的，但是我为产品表和地区表都制作了相应的表格。在表单 AfterInsert 事件上，我有以下代码：

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: 对此事进行了更多研究，我发现这是您需要用于触发器的代码如果您使用 SQL Server，如果您不想使用客户端设置。

显然，在 SQL Server 中，当您想要获取插入行的值时，您需要引用一个名为“inserted”的隐藏表。查看此链接以获取更多信息：DML 触发器的多行注意事项

太棒了！

产品表：

-- 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;

地区表：

-- 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;

Answer 4

您可以在运行时轻松构建“主表”：

SELECT  *
FROM    regiontable
CROSS JOIN
        producttable

它将更加高效，因为与物化主表不同，两个表都可能适合缓存。

如果由于某种原因您需要将其具体化，那么只需在两个表上编写触发器即可：

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

在 mastertable 和

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

producttable 上。