sql - 插入多个表

Question

相关域部分的简要说明:

类别由四个数据组成:

1. 性别(男/女)
2. 年龄划分(Mighty Mite到Master)
3. 皮带颜色(白色到黑色)
4. 体重划分(鸡到重)

因此，雄性成年黑公鸡构成了一个类别。有些组合可能不存在，例如强大的螨黑带。

一名运动员会与同一类别的运动员比赛，如果他进行分类，他会与不同体重级别的运动员比赛(但性别、年龄和腰带相同)。

到建模。我有一个 Category 表，其中已填充了域中存在的所有组合。

CREATE TABLE Category (
  [Id] [int] IDENTITY(1,1) NOT NULL,
  [AgeDivision_Id] [int] NULL,
  [Gender] [int] NULL,
  [BeltColor] [int] NULL,
  [WeightDivision] [int] NULL
)

一个CategorySet和一个CategorySet_Category，它与Category形成多对多关系。

CREATE TABLE CategorySet (
  [Id] [int] IDENTITY(1,1) NOT NULL,
  [Championship_Id] [int] NOT NULL,
)

CREATE TABLE CategorySet_Category (
  [CategorySet_Id] [int] NOT NULL,
  [Category_Id] [int] NOT NULL
)

给定以下结果集:

   | Options_Id | Championship_Id | AgeDivision_Id | BeltColor | Gender | WeightDivision |
   |------------|-----------------|----------------|-----------|--------|----------------|
1. | 2963       | 422             | 15             | 7         | 0      | 0              |
2. | 2963       | 422             | 15             | 7         | 0      | 1              |
3. | 2963       | 422             | 15             | 7         | 0      | 2              |
4. | 2963       | 422             | 15             | 7         | 0      | 3              |
5. | 2964       | 422             | 15             | 8         | 0      | 0              |
6. | 2964       | 422             | 15             | 8         | 0      | 1              |
7. | 2964       | 422             | 15             | 8         | 0      | 2              |
8. | 2964       | 422             | 15             | 8         | 0      | 3              |

因为运动员可能会对抗两个 CategorySet，所以我需要以两种不同的方式填充 CategorySet 和 CategorySet_Category(可以是两个查询):

每一行有一个 Category_Set，其中有一个 CategorySet_Category 指向相应的 Category。

一个 Category_Set，将同一 AgeDivision_Id、BeltColor、Gender 中的所有 WeightDivision 分组到一个 CategorySet 中。在此示例中，只有 BeltColor 有所不同。

因此最终结果总共有 10 个 CategorySet 行:

| Id | Championship_Id | 
|----|-----------------|
| 1  | 422             |
| 2  | 422             | 
| 3  | 422             |
| 4  | 422             | 
| 5  | 422             | 
| 6  | 422             |
| 7  | 422             |
| 8  | 422             |
| 9  | 422             |  /* groups different Weight Division for BeltColor 7 */
| 10 | 422             |  /* groups different Weight Division for BeltColor 8 */

并且 CategorySet_Category 将有 16 行:

| CategorySet_Id | Category_Id |
|----------------|-------------|
| 1              | 1           |
| 2              | 2           |
| 3              | 3           |
| 4              | 4           |
| 5              | 5           |
| 6              | 6           |
| 7              | 7           |
| 8              | 8           |
| 9              | 1           | /* groups different Weight Division for BeltColor 7 */
| 9              | 2           | /* groups different Weight Division for BeltColor 7 */
| 9              | 3           | /* groups different Weight Division for BeltColor 7 */
| 9              | 4           | /* groups different Weight Division for BeltColor 7 */
| 10             | 5           | /* groups different Weight Division for BeltColor 8 */
| 10             | 6           | /* groups different Weight Division for BeltColor 8 */
| 10             | 7           | /* groups different Weight Division for BeltColor 8 */
| 10             | 8           | /* groups different Weight Division for BeltColor 8 */

我不知道如何插入CategorySet，获取它生成的Id，然后用它插入CategorySet_Category

我希望我已经明确表达了我的意图。

我也 created a SQLFiddle .

编辑 1: 我在 Jacek 的回答中评论说这只会运行一次，但这是错误的。它将每周运行几次。我可以选择从 C# 或存储过程作为 SQL 命令运行。性能并不重要。

编辑 2: Jacek 建议使用 SCOPE_IDENTITY 返回 Id。问题是，SCOPE_IDENTITY 仅返回最后插入的 ID，而我在 CategorySet 中插入了不止一行。

编辑 3:回答 @FutbolFan，他询问如何检索 FakeResultSet。

这是一个表CategoriesOption(Id、Price_Id、MaxAthletesByTeam)

以及表CategoriesOptionBeltColor、CategoriesOptionAgeDivision、CategoriesOptionWeightDivison、CategoriesOptionGender。这四个表基本相同(Id、CategoriesOption_Id、Value)。

查询如下所示:

SELECT * FROM CategoriesOption co
LEFT JOIN CategoriesOptionAgeDivision ON 
    CategoriesOptionAgeDivision.CategoriesOption_Id = co.Id
LEFT JOIN CategoriesOptionBeltColor ON 
    CategoriesOptionBeltColor.CategoriesOption_Id = co.Id
LEFT JOIN CategoriesOptionGender ON 
    CategoriesOptionGender.CategoriesOption_Id = co.Id
LEFT JOIN CategoriesOptionWeightDivision ON 
    CategoriesOptionWeightDivision.CategoriesOption_Id = co.Id

Answer 1

此处描述的解决方案将在多用户环境中以及目标表 CategorySet 和 CategorySet_Category 不为空时正常工作。我使用了您的 SQL Fiddle 中的架构和示例数据.

第一部分很简单

(ab)使用MERGE与OUTPUT子句。

MERGE 可以INSERT、UPDATE 和DELETE 行。在我们的例子中，我们只需要INSERT。 1=0 始终为 false，因此 NOT MATCHED BY TARGET 部分始终会执行。一般来说，可能还有其他分支，请参阅文档。 WHEN MATCHED 通常用于UPDATE； WHEN NOT MATCHED BY SOURCE 通常用于DELETE，但我们在这里不需要它们。

这种复杂形式的 MERGE 相当于简单的 INSERT，但与简单的 INSERT 不同，它的 OUTPUT 子句允许引用我们需要的列。

MERGE INTO CategorySet
USING
(
    SELECT
        FakeResultSet.Championship_Id
        ,FakeResultSet.Price_Id
        ,FakeResultSet.MaxAthletesByTeam
        ,Category.Id AS Category_Id
    FROM
        FakeResultSet
        INNER JOIN Category ON
            Category.AgeDivision_Id = FakeResultSet.AgeDivision_Id AND
            Category.Gender = FakeResultSet.Gender AND
            Category.BeltColor = FakeResultSet.BeltColor AND
            Category.WeightDivision = FakeResultSet.WeightDivision
) AS Src
ON 1 = 0
WHEN NOT MATCHED BY TARGET THEN
INSERT
    (Championship_Id
    ,Price_Id
    ,MaxAthletesByTeam)
VALUES
    (Src.Championship_Id
    ,Src.Price_Id
    ,Src.MaxAthletesByTeam)
OUTPUT inserted.id AS CategorySet_Id, Src.Category_Id 
INTO CategorySet_Category (CategorySet_Id, Category_Id)
;

FakeResultSet 与 Category 连接以获取 FakeResultSet 每一行的 Category.id。假设 Category 具有 AgeDivision_Id、Gender、BeltColor、WeightDivision 的唯一组合。

在OUTPUT子句中，我们需要源表和目标表中的列。简单 INSERT 语句中的 OUTPUT 子句不提供它们，因此我们在这里使用 MERGE 来提供它们。

上面的 MERGE 查询将使用生成的 ID 将 8 行插入到 CategorySet 中，并插入 8 行到 CategorySet_Category 中。

第二部分

需要临时表。我将使用表变量来存储生成的 ID。

DECLARE @T TABLE (
    CategorySet_Id int
    ,AgeDivision_Id int
    ,Gender int
    ,BeltColor int);

我们需要记住生成的 CategorySet_Id 以及导致它的 AgeDivision_Id、Gender、BeltColor 的组合。

MERGE INTO CategorySet
USING
(
    SELECT
        FakeResultSet.Championship_Id
        ,FakeResultSet.Price_Id
        ,FakeResultSet.MaxAthletesByTeam
        ,FakeResultSet.AgeDivision_Id
        ,FakeResultSet.Gender
        ,FakeResultSet.BeltColor
    FROM
        FakeResultSet
    GROUP BY
        FakeResultSet.Championship_Id
        ,FakeResultSet.Price_Id
        ,FakeResultSet.MaxAthletesByTeam
        ,FakeResultSet.AgeDivision_Id
        ,FakeResultSet.Gender
        ,FakeResultSet.BeltColor
) AS Src
ON 1 = 0
WHEN NOT MATCHED BY TARGET THEN
INSERT
    (Championship_Id
    ,Price_Id
    ,MaxAthletesByTeam)
VALUES
    (Src.Championship_Id
    ,Src.Price_Id
    ,Src.MaxAthletesByTeam)
OUTPUT
    inserted.id AS CategorySet_Id
    ,Src.AgeDivision_Id
    ,Src.Gender
    ,Src.BeltColor
INTO @T(CategorySet_Id, AgeDivision_Id, Gender, BeltColor)
;

上面的 MERGE 会根据需要对 FakeResultSet 进行分组，并将 2 行插入到 CategorySet 中，将 2 行插入到 @T 中>.

然后将@T与Category连接以获得Category.IDs:

INSERT INTO CategorySet_Category (CategorySet_Id, Category_Id)
SELECT
    TT.CategorySet_Id
    ,Category.Id AS Category_Id
FROM
    @T AS TT
    INNER JOIN Category ON
        Category.AgeDivision_Id = TT.AgeDivision_Id AND
        Category.Gender = TT.Gender AND
        Category.BeltColor = TT.BeltColor
;

这将在 CategorySet_Category 中插入 8 行。