Использование курсора для обновления другой таблицы с использованием SQL Server, T-SQL?

Question

У меня есть таблица, для которой нужно выполнить первоначальное обновление, а затем - ночное обновление. У меня есть имя файла, которое мне нужно, чтобы получить значения из inorder для обновления двух столбцов. Я пришел из Oracle PL/SQL, и у меня мало опыта работы с SQL Server и T-SQL. Я хорошо разбираюсь в курсорах Oracle для обхода таблиц и понимаю, что они противоречивы для работы в SQL Server.

Моя конечная цель - предоставить пользователям информацию о том, сколько файлов каждого типа и типа Water Right было обработано.

Имена файлов выглядят так:

UPDATE_TO_FILE_STATEMENT_OF_CLAIM_41L_44799_00_2780.pdf
PRELIMINARY_DECREE_STATEMENT_OF_CLAIM_41G_40643_00_1134.pdf
FILE_STOCKWATER_PERMIT_40N_30116114_10437.pdf
UPDATE_TO_FILE_GROUND_WATER_CERTIFICATE_40E_30025354_10399.pdf

Из этого имени файла мне нужно вытащить тип файла и тип Water Right. Существует 4 типа файлов и 20 Water Right. Я мог бы использовать Case, но тогда я должен изменить код, если они добавляют тип файла или тип Water Right. Разбор имен тоже не простая задача. Я создал таблицу с кодами, которые я буду использовать, как будет выглядеть имя файла, описание и будет ли это тип файла или тип права воды.

FILE_CD FILE_NAME                       DESCR                       FILE_TYPE
----------------------------------------------------------------------------
62GW    62-73_GROUND_WATER_RECORD       62-73 GROUND WATER RECORD       WT
CDWR    CONSERVATION_DISTRICT_RECORD    CONSERVATION DISTRICT RECORD    WT
UPDT    UPDATE_TO_FILE_                 UPDATE TO FILE                  FT

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

Это мой код:

BEGIN TRANSACTION

DECLARE @F_type NVARCHAR(2)
DECLARE @F_name NVARCHAR(30)
DECLARE @F_CD NVARCHAR(4)

-- local means the cursor name is private to this code
-- fast_forward enables some speed optimizations
DECLARE c_TUpdt CURSOR LOCAL FAST_FORWARD FOR
   SELECT 
       FILE_CD, FILE_NAME, FILE_TYPE
   FROM 
       PRDECMSTATS.dbo.FileType 
   WHERE 
       FILE_CD NOT IN ('OTHT', 'OTHF')

OPEN c_TUpdt

FETCH NEXT FROM c_TUpdt INTO @F_CD, @F_name, @F_type

WHILE @@fetch_status = 0
BEGIN
    -- CHECK FILE TYPE
    IF @F_type = 'FT' THEN
       UPDATE PRDECMSTATS.dbo.FileDetails 
       SET File_Type = @F_CD
       WHERE File_Type IS NULL
         AND FileNAME LIKE @F_name || '%'
    END IF

    IF @F_type = 'WT' THEN
       UPDATE PRDECMSTATS.dbo.FileDetails 
       SET WR_Type = @F_CD
       WHERE WR_Type IS NULL
         AND FileNAME LIKE '%' || @F_name || '%'
    END IF

    FETCH NEXT FROM c_TUpdt INTO @F_CD, @F_name, @F_type
END

CLOSE c_TUpdt
DEALLOCATE c_TUpdt

/* need to then traverse for other types, these will need to be corrected by hand at some point */
UPDATE PRDECMSTATS.dbo.FileDetails 
SET File_Type = 'OTHF'
WHERE File_Type IS NULL

UPDATE PRDECMSTATS.dbo.FileDetails 
SET WR_Type = 'OTHT'
WHERE WR_Type IS NULL

COMMIT TRANSACTION

Я ожидаю, что это обновит 2 столбца, учитывая данные в имени файла.

ЭТО РАБОТАЕТ? Есть лучший способ сделать это?

Answer 1

Курсоры могут работать, но почти никогда не являются наиболее эффективным ответом. Я считаю, что это полная замена для вашего курсора

UPDATE fd
SET File_Type = ft.FILE_CD
FROM PRDECMSTATS.dbo.FileDetails fd
INNER JOIN PRDECMSTATS.dbo.FileType ft ON ft.FILE_CD NOT IN ('OTHT', 'OTHF')
    AND ft.FILE_TYPE='FT'
    AND fd.File_Type IS NULL
    AND fd.FileNAME LIKE ft.FILE_NAME + '%'

UPDATE fd
SET WR_Type = ft.FILE_CD
FROM PRDECMSTATS.dbo.FileDetails fd
INNER JOIN PRDECMSTATS.dbo.FileType ft ON ft.FILE_CD NOT IN ('OTHT', 'OTHF')
    AND ft.FILE_TYPE='WT'
    AND fd.WR_Type IS NULL
    AND fd.FileNAME LIKE  '%' + ft.FILE_NAME + '%'

Это может быть сведено к одному утверждению, но я думаю, что это сделает его менее разборчивым. Кстати, оператор "+" объединяется в SQL Server. Кроме того, первое UPDATE не имеет подстановочного знака в начале строки, как второе; не уверен, что это намеренно.

Расширение за пределы рассматриваемого курсора, это может заменить все

UPDATE fd
SET File_Type = ISNULL(ft.FILE_CD,'OTHF')
FROM PRDECMSTATS.dbo.FileDetails fd
LEFT JOIN PRDECMSTATS.dbo.FileType ft ON ft.FILE_CD NOT IN ('OTHT', 'OTHF')
    AND ft.FILE_TYPE='FT'
    AND fd.FileNAME LIKE ft.FILE_NAME + '%'
WHERE fd.File_Type IS NULL

UPDATE fd
SET WR_Type = ISNULL(ft.FILE_CD,'OTHT')
FROM PRDECMSTATS.dbo.FileDetails fd
LEFT JOIN PRDECMSTATS.dbo.FileType ft ON ft.FILE_CD NOT IN ('OTHT', 'OTHF')
    AND ft.FILE_TYPE='WT'
    AND fd.FileNAME LIKE  '%' + ft.FILE_NAME + '%'
WHERE fd.WR_Type IS NULL

Answer 2

Конечно, вы можете использовать курсор для этого, но вы можете сделать все это за один проход данных (намного, намного, намного эффективнее). Сравните одну поездку в продуктовый магазин, чтобы получить все, и сходить в магазин за молоком, поехать домой, вернуться в магазин за хлебом, домой и так далее.

;WITH ft AS
(
  SELECT FILE_CD, FILE_TYPE, 
    Pattern = CASE FILE_TYPE WHEN 'WT' THEN '%' ELSE '' END + FILE_NAME + '%'
  FROM PRDECMSTATS.dbo.FileType
  WHERE FILE_CD NOT IN ('OTHT','OTHF')
)
UPDATE f SET 
  FILE_TYPE = CASE WHEN ft.FILE_TYPE = 'FT' THEN ft.FILE_CD ELSE 'OTHF' END,
  WR_TYPE = CASE WHEN ft.FILE_TYPE = 'WT' THEN ft.FILE_CD ELSE 'OTHT' END
FROM PRDECMSTATS.dbo.FileDetails AS f
LEFT OUTER JOIN ft -- changed this
ON f.FileNAME LIKE ft.pattern;

Теперь ваш вопрос имеет следующую логику:

UPDATE PRDECMSTATS.dbo.FileDetails 
SET File_Type = 'OTHF'
WHERE File_Type IS NULL

UPDATE PRDECMSTATS.dbo.FileDetails 
SET WR_Type = 'OTHT'
WHERE WR_Type IS NULL

Вы жаловались, что я устанавливаю File_Type в OTHF, но это именно то , что вы уже делали! Вот результат, который я получаю:

С предоставленными вами примерами данных и логикой, которую вы написали в своем псевдо-курсоре, я не знаю, как вы могли бы получить:

• любое значение, отличное от OTHT в WR_Type (поскольку ни один файл, указанный вами в вопросе, не соответствует шаблону для WT)
• OTHT в File_Type вообще (ни ваш курсор, ни мой код не могут это сделать)
• OTHF в WR_Type вообще (ни ваш курсор, ни мой код также не могут это сделать)

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