Обновить записи на основе вставленных идентификаторов и другого неисходного столбца в SQL.

Возможно, на него уже есть ответ, но я не смог его найти... Итак, у меня есть 2 таблицы и данные в третьей. Назовем их (Source, Target и UpdateTarget).

Мне нужно вставить записи из источника в цель, затем получить автоматически увеличивающиеся идентификаторы из цели и обновить таблицу UpdateTarget с этими идентификаторами на основе фильтров из исходной таблицы. Я пытался использовать OUTPUT, но выдает ошибку:

Не удалось связать составной идентификатор "s.EmployeeID".

Вот мой текущий SQL-запрос:

CREATE TABLE dbo.target
(
    id INT IDENTITY(1,1) PRIMARY KEY,
    employee VARCHAR(32)
);

CREATE TABLE dbo.source
(
    id INT IDENTITY(1,1) PRIMARY KEY,
    employee VARCHAR(32),
    EmployeeID int
);

CREATE TABLE dbo.updateTarget
(
    id INT IDENTITY(1,1) PRIMARY KEY,
    ExternalID int
);

DECLARE @MyTableVar TABLE
(
    id INT,
    EmployeeID int
);

    INSERT dbo.target (employee)
    OUTPUT 
        inserted.id, -- autoincremented ID
        s.EmployeeID -- here i got an error
        INTO @MyTableVar
    SELECT s.employee
    FROM dbo.source AS s

    UPDATE dbo.updateTarget
    SET ExternalID = data.ID
    FROM @MyTableVar data
    WHERE updateTarget.ID = data.EmployeeID


DROP TABLE source
DROP TABLE target
DROP TABLE updateTarget

У меня нет столбца EmployeeID в целевой таблице.

Есть ли способ добиться этого, не делая два запроса для каждой записи? Или вы можете указать мне на существующий ответ, если таковой имеется?

Спасибо!


sql sql-server insert-update sql-insert sql-update
person Sergey Litvinov    schedule 20.08.2015    source источник

Ответы (1)


arrow_upward
2
arrow_downward

1) ВСТАВИТЬ В табличную переменную, сгенерированную идентификатором, и EmployeeId для использования в обновлении

2) MERGE вместо INSERT (позволяет получить столбец EmployeeId из SRC)

3) Результат OUTPUT, добавлено действие, получен идентификатор от TGT и EmployeeId

INSERT INTO @MyTableVar(id, EmployeeId)
SELECT id, EmployeeId 
FROM (
    MERGE dbo.target TGT
    USING dbo.source SRC
    ON TGT.employee = SRC.employee
    WHEN NOT MATCHED THEN
       INSERT (employee)
       VALUES (src.employee)
    OUTPUT inserted.id, SRC.EmployeeId)
AS out(id, EmployeeId);;

Объединение обеспечивает лучшие параметры ВЫВОДА

person Lukasz Szozda    schedule 20.08.2015
comment
Удивительно! вы мне очень помогли. Большое спасибо! - person Sergey Litvinov; 20.08.2015
comment
Добро пожаловать. Помните, что CTE и MERGE — это швейцарский армейский нож для TSQL :) - person Lukasz Szozda; 20.08.2015