Комплексное слияние TSQL

Я «унаследовал» отличный кусок кода TSQL, который делает следующее:

  • Построчно перемещается по курсору.
  • Курсор содержит данные, которые необходимо объединить (обновить) в таблице A
  • For each row loop in the cursor a stored proc is called. The proc:
    • If a corresponding row exists in Table A then it is updated
    • If such a row does not exist then:
      • Inserts a single row in in a different Table B.
      • Получает вновь сгенерированный идентификатор (например, IDB)
      • Вставляет одну строку в таблицу A. Для вставки таблицы A требуется IDB (поле не является нулевым, предполагается, что оно может иметь значения ТОЛЬКО из таблицы B, но ограничение FK отсутствует)

Очевидно, это отстой (из соображений производительности и элегантности) !!

Вопрос. Сначала это выглядит как стандартный случай использования MERGE. Я пробовал делать:

MERGE [dbo].[TableA] AS Target
USING <cursor data set as a select statement> as Src on target.IDA = Src.IDA
WHEN MATCHED 
  //update
WHEN NOT MATCHED
//insert <------ Fails because obviously a new IDB is required

Также пробовал различные подходы, такие как nested select that sends IDB on the OUTPUT, но это не удалось, потому что IDB - это ПК.

Другие виды слияний также потерпели неудачу, например:

MERGE Table A with <cursor data set as a select statement>
...
MERGE Table A with Table B
WHEN NOT MATCHED
//insert on Table A
WHEN NOT MATCHED
// Update Table B

У кого-нибудь есть идеи по этому поводу? По сути, я думаю, что если мы обобщим вопрос, это будет:

Can I insert and return the PK in one statement that can be nested in other statements

Заранее благодарим за любые ответы

Георгий


merge sql-server tsql sql-server-2008 upsert
person George    schedule 21.09.2011    source источник
comment
Я думаю, вы можете объединиться в tableB и использовать технику здесь.   -  person Martin Smith    schedule 21.09.2011
comment
Спасибо, видел это. На самом деле это хорошая идея. По сути, парень хранит вновь сгенерированные идентификаторы во временных переменных. Не то же самое, что я искал (выполнить весь процесс в 1 Merge), но это все же интересный подход. Спасибо, Мартин.   -  person George    schedule 21.09.2011
comment
+1 за использование слова 'upserted' ... :)   -  person M.R.    schedule 21.09.2011
comment
Что такое IDB? Можно ли изменить этот IDB на автоинкремент или значение по умолчанию somesort? Кроме того, можете ли вы по существу обернуть акт создания нового идентификатора в функцию, а затем использовать его в своем операторе слияния?   -  person M.R.    schedule 21.09.2011

Ответы (2)


arrow_upward
3
arrow_downward

Если у вас есть автоматически сгенерированный ПК на TableB, вы можете использовать код, подобный этому. В противном случае просто измените INSERT на TableA, чтобы сначала получить PK из TableB.

DECLARE @OldData CHAR(10)
SET @OldData = 'Old'
DECLARE @NewData CHAR(10)
SET @NewData = 'New'

CREATE TABLE #TableA 
(
    IDA INT IDENTITY(1,1) PRIMARY KEY,
    IDB INT NOT NULL,
    DataA CHAR(10)
)

CREATE TABLE #TableB 
(
    IDB INT IDENTITY(1,1) PRIMARY KEY,
    DataB CHAR(10)
)

DECLARE @IDsToUpsert TABLE
(
    ID INT
)

-- Add test values for existing rows 
INSERT INTO #TableB
OUTPUT INSERTED.IDB, @OldData
INTO #TableA
SELECT @OldData UNION ALL
SELECT @OldData UNION ALL
SELECT @OldData UNION ALL
SELECT @OldData 

-- Add test values for the rows to upsert
INSERT INTO @IDsToUpsert
SELECT 1 UNION -- exists
SELECT 3 UNION -- exists
SELECT 5 UNION -- does not exist
SELECT 7 UNION -- does not exist
SELECT 9       -- does not exist

-- Data Before
SELECT * From #TableA
SELECT * From #TableB

DECLARE rows_to_update CURSOR
    FOR SELECT ID FROM @IDsToUpsert

DECLARE @rowToUpdate INT
DECLARE @existingIDB INT

OPEN rows_to_update;

FETCH NEXT FROM rows_to_update 
INTO @rowToUpdate;

WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRANSACTION

        IF NOT EXISTS 
        (
            SELECT 1 FROM #TableA WITH (UPDLOCK, ROWLOCK, HOLDLOCK)
            WHERE IDA = @rowToUpdate            
        )
        BEGIN
            -- Insert into B, then insert new val into A
            INSERT INTO #TableB
            OUTPUT INSERTED.IDB, INSERTED.DataB 
            INTO #TableA
            SELECT @NewData
            -- Change code here if PK on TableB is not autogenerated
        END
        ELSE
        BEGIN
            -- Update
            UPDATE #TableA
            SET DataA = @NewData
            WHERE IDA = @rowToUpdate
        END

    COMMIT TRANSACTION

    FETCH NEXT FROM rows_to_update 
    INTO @rowToUpdate;
END

CLOSE rows_to_update;
DEALLOCATE rows_to_update;

SELECT * FROM #TableA
SELECT * FROM #TableB

DROP TABLE #TableA
DROP TABLE #TableB
person GalacticJello    schedule 21.09.2011
comment
Я думаю, это очень хороший ответ; этот ‹100-балльный плакат благодарит вас ;-) - person George; 22.09.2011

arrow_upward
1
arrow_downward

Чтобы ответить на ваш общий вопрос - «Могу ли я вставить и вернуть ПК в один оператор, который может быть вложен в другие операторы» - да, безусловно. Но это зависит от логики создания вашего ПК. В этом случае похоже, что для создания PK вам нужно вставить в другую таблицу, а затем получить оттуда вновь сгенерированный идентификатор. Это не очень эффективно (ИМХО), если для этого нет очень конкретной причины. Автоинкременты, GUID и т. Д., Как правило, лучше работают как PK. Если вы можете упростить / изменить логику, стоящую за этим, и вы можете найти более простой способ сделать это, так что PK 'CAN' будет сгенерирован в одном статуте / функции и, следовательно, может использоваться в других операторах.

person M.R.    schedule 21.09.2011