Вставка в несколько таблиц

Ответ 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, чтобы получить Category.id для каждой строки FakeResultSet. Предполагается, что Category имеет уникальные комбинации AgeDivision_Id, Gender, BeltColor, WeightDivision.

В разделе OUTPUT нам нужны столбцы из исходных и целевых таблиц. Предложение OUTPUT в простой инструкции INSERT не предоставляет их, поэтому мы используем MERGE здесь.

В приведенном выше запросе MERGE вставка 8 строк в CategorySet и вставка 8 строк в CategorySet_Category с использованием сгенерированных идентификаторов.

Вторая часть

требуется временная таблица. Я буду использовать переменную таблицы для хранения сгенерированных идентификаторов.

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
;

Это вставляет 8 строк в CategorySet_Category.

Ответ 2

Вот не полный ответ, но направление, которое вы можете использовать для решения этой проблемы:

1-й запрос:

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, таким образом, вы получите реальный идентификатор, который будет вставлен в Строка CategorySet для второго запроса

Ответ 3

То, что я делаю, когда сталкиваюсь с этими ситуациями, заключается в создании одной или нескольких временных таблиц с помощью row_number() над предложениями, дающими мне идентификаторы во временных таблицах. Затем я проверяю наличие каждой записи в фактических таблицах, и если они существуют, обновите временную таблицу с фактическими идентификаторами записей. Наконец, я запустил цикл while во временных таблицах, в которых отсутствует фактический идентификатор и вставляем их по одному, после вставки я обновляю временную запись таблицы с фактическими идентификаторами. Это позволяет вам управлять всеми данными контролируемым образом.

Ответ 4

@@IDENTITY - ваш друг во второй части вопроса https://msdn.microsoft.com/en-us/library/ms187342.aspx а также Лучший способ получить идентификатор вставленной строки?

Некоторые API (драйверы) возвращают функцию int из функции update(), то есть идентификатор, если он "вставляет". Какой API/среда вы используете?

Я не понимаю 1-ю проблему. Вы не должны вставлять столбец идентификаторов.

Ответ 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