Вопрос об импорте данных: следует ли использовать курсор?

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

Как часто вы импортируете эти файлы?

У меня был бы процесс импорта, который в основном является автономным. Он может использовать хранимые процедуры или таблицы в базе данных, но я бы не стал использовать триггеры. Простой подход будет примерно таким, как показано ниже. Я добавил столбец в Legacy_Invoices (также переименованный во что-то более описательное), чтобы вы могли отслеживать, когда элементы были импортированы и откуда. Вы можете расширить это, чтобы отслеживать больше информации, если это необходимо.

Кроме того, я не понимаю, как вы отслеживаете номера счетов в своем коде. Я предположил, что столбец IDENTITY в таблице Legacy_Invoices. Этого почти наверняка недостаточно, поскольку я предполагаю, что вы также создаете счета в своей собственной системе (вне устаревшей системы). Однако, не зная вашей схемы нумерации счетов, невозможно дать решение.

BEGIN TRAN

DECLARE
    @now DATETIME = GETDATE()

UPDATE Legacy_Invoices
SET
    import_datetime = @now
WHERE
    import_status = 'Awaiting Import'

INSERT INTO dbo.Invoices (invoice_no, cust_no)
SELECT DISTINCT invoice_no, cust_no
FROM
    Legacy_Invoices
WHERE
    import_datetime = @now

UPDATE Legacy_Invoices
SET
    import_status = 'Invoice Imported'
WHERE
    import_datetime = @now

INSERT INTO dbo.Invoice_Lines (invoice_no, item_no, item_qty, item_prc)
SELECT
    invoice_no,
    item_no_1,
    item_qty_1,
    item_prc_1
FROM
    Legacy_Invoices LI
WHERE
    import_datetime = @now AND
    import_status = 'Invoice Imported' AND
    item_no_1 IS NOT NULL

UPDATE Legacy_Invoices
SET
    import_status = 'Item 1 Imported'
WHERE
    import_datetime = @now AND
    import_status = 'Invoice Imported'

<Repeat for item_no_2 through 7>

COMMIT TRAN

Но есть большое предостережение. Хотя курсоры обычно нежелательны в SQL, и вы хотите использовать обработку на основе наборов вместо обработки RBAR (строка за строкой), импорт данных часто является исключением.

Проблема с вышеизложенным заключается в том, что если одна строка терпит неудачу, весь этот шаг импорта завершается неудачей. Кроме того, очень сложно запустить один объект (счет-фактуру и отдельные позиции) с помощью бизнес-логики, когда вы импортируете их массово. Это то место, где SSIS действительно сияет. Это очень быстро (при условии, что вы правильно его настроили), даже при импорте одного объекта за раз. Затем вы можете поместить в него все виды обработки ошибок, чтобы убедиться, что импорт проходит гладко. В одной строке импорта указан неверный номер счета-фактуры? Нет проблем, отметьте это как ошибку и двигайтесь дальше. В строке есть элемент № 2, но нет элемента № 1 или цена указана без количества? Нет проблем, отметьте ошибку и двигайтесь дальше.

Для одиночного импорта я мог бы придерживаться приведенного выше кода (конечно, добавляя соответствующую обработку ошибок), но для повторяющегося процесса я почти наверняка использовал бы SSIS. Вы можете импортировать миллионы строк за секунды или минуты даже с индивидуальной обработкой ошибок для каждого бизнес-объекта.

Если у вас есть какие-либо проблемы с запуском SSIS (учебники есть по всему Интернету и в MSDN в Microsoft), опубликуйте здесь любые проблемы, и вы получите быстрые ответы.

Я не уверен, почему вы добавили триггер. Будете ли вы продолжать использовать LEGACY_TABLE_SQL? Если нет, то как насчет этой одноразовой процедуры? Он использует синтаксис Oracle, но может быть адаптирован к большинству баз данных.

ПРОЦЕДУРА МИГРАЦИИ

CURSOR all_data is
SELECT invoice_no, cust_no,Item_1_no,Item_1_qty........
FROM LEGACY_TABLE_SQL;

НАЧИНАТЬ

ДЛЯ данных в all_data LOOP INSERT INTO INVOICE (invoice_no, cust_no) VALUES (data.invoice_no, data.cust_no); ЕСЛИ Item_1_no НЕ НУЛЬ, ТОГДА ВСТАВИТЬ В INVOICE_LINE_ITEM(invoice_no,Item_1_no,Item_1_qty....) VALUES(data.invoice_no,data.Item_1_no,data.Item_1_qty....) END IF; --дальнейшие вставки для каждого элемента

КОНЕЦ ЦИКЛ;
СОВЕРШИТЬ;
КОНЕЦ;

Это можно дополнительно оптимизировать в Oracle с помощью BULK_COLLECT. Я бы создал таблицу INVOICE_LINE_ITEM со значениями по умолчанию 0 для всех элементов.

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

РЕДАКТИРОВАТЬ: поскольку вы советуете, что будете продолжать использовать устаревшую таблицу, вам нужно расставить приоритеты в том, что вы хотите. Является ли эффективность и производительность вашим приоритетом номер один, ремонтопригодность, синхронная транзакция. Например:
- если производительность не очень важна, тогда реализуйте это, как вы описали - если это необходимо будет поддерживать, вы можете больше инвестировать в кодирование - если вам не требуется синхронная транзакция, вы можете добавить в свой LEGACY_TABLE_SQL столбец под названием «обработано» со значением по умолчанию 0. Затем один раз в день или час запланируйте задание, чтобы получить все заказы, которые не были обработаны.