Блог

У меня есть 3 таблицы (упрощенные для вопроса), подобные следующим:

 Candidate
   - ID (primary key auto-numbered)
   - ProcessedOn
   - CandidateState

CandidateData
   - CandidateID
   - FieldKey
   - FieldValue

CandidateTransform
   - ID (primary key auto-numbered)
   - FieldKey 
   - FieldValue
  

CandidateTransform содержит данные, которые используются для заполнения CandidateData (т.е. FieldKey -> FieldKey и FieldValue -> Значение поля). Значение поля в CandeidateTransform содержит строку SQL, которая выбирает значение поля из другой базы данных / таблицы (информация о персонале) следующим образом:

 (SELECT firstName FROM Personnel WHERE id = @CandidateID)
  

Я мог сделать это ранее, поскольку мы одновременно обрабатывали только одного кандидата и создавали хранимую процедуру с инструкцией INSERT, подобной этой:

 DECLARE @NewCandidate TABLE (id bigint);

INSERT CloudRecruiting.Candidate (CandidateState, ActHdrKey, ActionProcessedOn, JobPostingID, Deleted)
OUTPUT INSERTED.CandidateKey INTO @NewCandidate
VALUES (0, 0, GETDATE(), @PlacementID, 0);

DECLARE @ID bigint;
SET @ID = (SELECT id FROM @NewCandidate);

DECLARE @CandidateID bigint;
SET @CandidateID = (SELECT candidate FROM Personnel WHERE id = @PlacementID);

INSERT INTO .CandidateData (CandidateKey, FieldKey, FieldValue) 
    VALUES (@ID, 1128, (SELECT ISNULL(LEFT(CONVERT(VARCHAR, dateOfBirth, 120), 10), '') FROM Personnel WHERE id = @CandidateID))
    , (@ID, 1159, (SELECT email FROM Personnel WHERE id = @CandidateID))
    , (@ID, 1284, (SELECT ssn FROM Personnel WHERE id = @CandidateID))
    , (@ID, 1303, CAST((SELECT payRate FROM Personnel WHERE id = @PlacementID) as nvarchar(MAX)))
    , (@ID, 1169, (SELECT firstName FROM Personnel WHERE id = @CandidateID))
    , (@ID, 1229, (SELECT middleName FROM Personnel WHERE id = @CandidateID))
    , (@ID, 1219, (SELECT lastName FROM Personnel WHERE id = @CandidateID))
    (etc.)
  

Теперь мы хотим сделать процесс более надежным, сохранив инструкции SELECT в таблице (CandidateTransform), которую клиент может изменять и иметь возможность обрабатывать несколько кандидатов в одном пакете с помощью хранимой процедуры, подобной этой (в настоящее время у нас есть SQL, который фиксирует идентификаторы кандидатов, которые нам нужно обработать, и сохраняет их во временной таблице) :

 for each (candidateID in tempTable)
   create entry in Candidate
   capture new id number from Candidate

   for each (sql in CandidateTransform)
      execute sql script
      store value in CandidateData with the new id, FieldKey and FieldValue
   end
end
  

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