向多张表插入数据

Question

关于相关领域部分的简要说明：

一个类别由四个数据组成：

1. 性别（男/女）
2. 年龄段（小童到大师）
3. 带色（白带到黑带）
4. 体重级别（雏鸡到重量级）

因此，“男性成年人黑色雏鸡”形成一个类别。有些组合可能不存在，比如小童黑带。

运动员与同一类别的运动员比赛，如果他晋级，他将与不同体重级别但相同性别，年龄和带色的其他运动员进行比赛。

进入建模。我有一个已经填充了该领域中所有可能存在的组合的“类别”表。

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_Category（可以是两个查询）：

一行一个Category_Set，一个CategorySet_Category指向相应的Category。

一个Category_Set将相同年龄组、腰带颜色和性别的所有重量级别分组在一个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中。

我希望我已经表达清楚了我的意图。

我还创建了一个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

- Ortiga

---

@FutbolFan 就像我说的那样，它可以是两个查询。对于虚假结果集中的每一行，都有1个CategorySet和1个CategorySet_Category，总共有8行。对于每个BeltColor，还有1个CategorySet，因此需要两个，以及8个CategorySet_Category行。 - Ortiga

@FutbolFan 如果第一个提供了，也许我可以写第二个，尽管我在 SQL 方面缺乏技能。 - Ortiga

好的，那让我这样问你，你是如何获取那些“假”的结果来填充的呢？ - FutbolFan

@FutbolFan 请查看第三次编辑。 - Ortiga

我已经发布了我认为是您第一个需求的可行解决方案。我正在处理第二个需求，即填充“categoryset_category”表。希望这有所帮助！ - FutbolFan

Answer 1

这里描述的解决方案在多用户环境下和目标表 CategorySet 和 CategorySet_Category 不为空时可以正常工作。 我使用了您的 SQL Fiddle 中的模式和示例数据。 第一部分很直接 滥用 MERGE 与 OUTPUT 子句。 MERGE 可以插入、更新和删除行。在我们的情况下，我们只需要插入。 1=0 总是为假，因此始终执行 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” 查询将向 “CategorySet” 插入 8 行，并使用生成的 ID 向 “CategorySet_Category” 插入 8 行。

第二部分需要临时表。我将使用一个表变量来存储生成的 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行。

- Vladimir Baranov

---

有一件事我不明白，1 = 0 是用来做什么的？ - Ortiga

MERGE 可以 INSERT、UPDATE 和 DELETE 行。在我们的情况下，我们只需要 INSERT。1=0 总是为假，因此 NOT MATCHED BY TARGET 部分总是被执行。一般来说，可能会有其他分支，但我们这里不需要它们。这种复杂形式的 MERGE 等同于简单的 INSERT，但与简单的 INSERT 不同，它的 OUTPUT 子句允许引用我们需要的列。 - Vladimir Baranov

Answer 2

当我遇到这种情况时，我会使用row_number() over子句在一个或多个临时表中创建一个id，以便对临时表中的记录进行检查。然后，我检查每个实际表中的记录是否存在，如果存在，就用实际记录id更新临时表。最后，我在缺少实际id的临时表记录上运行一个while exists循环，逐个插入它们，在插入后我会更新临时表记录中的实际ids。这样可以控制地处理所有数据。

- SumGuy

Answer 3

这里并非完整答案，而是您可用于解决此问题的方向：

第一个查询：

select row_number() over(order by t, Id)  as n, Championship_Id
from (
select distinct 0 as t, b.Id, a.Championship_Id
from FakeResultSet as a
inner join
Category as b
on
a.AgeDivision_Id=b.AgeDivision_Id and
a.Gender=b.Gender and
a.BeltColor=b.BeltColor and
a.WeightDivision=b.WeightDivision
union all
select distinct 1, BeltColor, Championship_Id
from FakeResultSet
) as q

第二个查询：

select q2.CategorySet_Id, c.Id as Category_Id from (
select row_number() over(order by t, Id)  as CategorySet_Id, Id, BeltColor
from (
    select distinct 0 as t, b.Id, null as BeltColor
    from FakeResultSet as a
    inner join
    Category as b
    on
    a.AgeDivision_Id=b.AgeDivision_Id and
    a.Gender=b.Gender and
    a.BeltColor=b.BeltColor and
    a.WeightDivision=b.WeightDivision
    union all
    select distinct 1, BeltColor, BeltColor
    from FakeResultSet
) as q
) as q2
inner join
Category as c
on
(q2.BeltColor is null and q2.Id=c.Id)
OR
(q2.BeltColor = c.BeltColor)

当然，这只适用于空的“CategorySet”和“CategorySet_Category”表，但您可以使用“select coalese(max(Id), 0) from CategorySet”获取当前编号，并将其添加到“row_number”中，从而获得实际ID，该ID将插入第二个查询的“CategorySet”行中。

- Iłya Bursov

Answer 4

@@IDENTITY是第二个问题的好帮手。请参考https://msdn.microsoft.com/en-us/library/ms187342.aspx和Best way to get identity of inserted row?

一些API（驱动程序）从update()函数返回int，例如如果是“insert”，则返回ID。您使用的是哪种API/环境？

我不理解第一个问题。您不应该插入identity列。

- Jacek Cz

---

但是我如何获得正确的Id呢？如果我插入两行，并且select SCOPE_IDENTITY()，它将返回最后生成的2。在第二个表中，我需要为1和2都插入数据。 - Ortiga

这是一个一次性的导入脚本，将在 MSSQL studio 中执行。 - Ortiga

你可以使用 OUTPUT 子句。 - HLGEM

Answer 5

以下查询将为CategorySet行给出最终结果：

SELECT 
       ROW_NUMBER () OVER (PARTITION BY  Championship_Id ORDER BY Championship_Id) RNK,
       Championship_Id

FROM
(
    SELECT 
            Championship_Id
            ,BeltColor
    FROM #FakeResultSet
    UNION ALL
    SELECT 
            Championship_Id,BeltColor
    FROM #FakeResultSet
    GROUP BY Championship_Id,BeltColor
)BASE    

- Ashish Kumar