Updating existing temp table with id created during set based insert

Question

I have extracted some XML into a temporary table as follows:

declare @INT_ParticipantID INT = 1
declare @XML_Results XML = '
<roots>
  <root>
    <ID />
    <ResultDateTime>2016-08-16T13:58:21.484Z</ResultDateTime>
    <Test>
      <ID>5</ID>
      <ParticipantID>0</ParticipantID>
      <Instrument />
      <ControlSet />
      <Assay />
      <CreationDate>0001-01-01T00:00:00Z</CreationDate>
      <StartDate>0001-01-01T00:00:00Z</StartDate>
      <EndDate>0001-01-01T00:00:00Z</EndDate>
      <Closed>false</Closed>
      <SlideGenNumber>0</SlideGenNumber>
    </Test>
    <EnteredByInitials />
    <ControlSetLots />
    <LotResult1 />
    <LotResult2 />
    <LotResult3 />
    <LotResults>
      <ID>13</ID>
      <LotNumber />
      <LotName />
      <ExpiryDate>0001-01-01T00:00:00Z</ExpiryDate>
      <Result>
        <ID />
        <Count>1</Count>
        <Mean>2</Mean>
        <SD>3</SD>
      </Result>
      <ParticipantID>0</ParticipantID>
      <ApprovalStatus>false</ApprovalStatus>
      <LotAnalytes />
      <LotInstruments />
      <TestDetails />
    </LotResults>
    <LotResults>
      <ID>14</ID>
      <LotNumber />
      <LotName />
      <ExpiryDate>0001-01-01T00:00:00Z</ExpiryDate>
      <Result>
        <ID />
        <Count>4</Count>
        <Mean>5</Mean>
        <SD>6</SD>
      </Result>
      <ParticipantID>0</ParticipantID>
      <ApprovalStatus>false</ApprovalStatus>
      <LotAnalytes />
      <LotInstruments />
      <TestDetails />
    </LotResults>
    <LotResults>
      <ID>0</ID>
      <LotNumber />
      <LotName />
      <ExpiryDate>0001-01-01T00:00:00Z</ExpiryDate>
      <Result>
        <ID />
        <Count>1</Count>
        <Mean>0</Mean>
        <SD>0</SD>
      </Result>
      <ParticipantID>0</ParticipantID>
      <ApprovalStatus>false</ApprovalStatus>
      <LotAnalytes />
      <LotInstruments />
      <TestDetails />
    </LotResults>
    <StandardComment>
      <ID>1</ID>
      <EnteredBy />
      <Description />
    </StandardComment>
    <ReviewComment>
      <ID />
      <EnteredBy />
      <Description />
    </ReviewComment>
  </root>
</roots>
'

  SELECT r.value('ID[1]','int') AS Transaction_ID
        ,r.value('ResultDateTime[1]', 'datetime') AS Transaction_DateTime
        ,r.value('(Test/ID)[1]', 'int') AS QCTest_ID
        ,lr.value('ID[1]','int') AS Lot_ID
        ,lr.value('(Result/Count)[1]','int') AS Result_Count
        ,lr.value('(Result/Mean)[1]','decimal(18, 8)') AS Result_Mean
        ,lr.value('(Result/SD)[1]','decimal(18, 8)') AS Result_SD
        ,r.value('(StandardComment/ID)[1]','int') AS StandardComment_ID 
        ,r.value('(ReviewComment/ID)[1]','int') AS ReviewComment_ID 
    INTO #tempRawXML
    FROM @XML_Results.nodes('/roots/root') AS A(r)
   CROSS 
   APPLY r.nodes('LotResults') AS B(lr)  

This brings me back the result set below:

I need to insert the results extracted into two tables - one is a mapping table and the other is determined by the Lot_ID field sent through the XML.

What I need to achieve is an INSERT into the mapping table, then extract the newly generated primary key field (which is an IDENTITY) and INSERT it into the relevant table(s) along with the remaining result data.

The most efficient way I can think to do this would be to UPDATE the existing Transaction_ID column in the #tempRawXML table with the OUTPUT of the first INSERT operation. Is there a way I can achieve this? So far I have the following - which creates a new row in the #tempRawXML table with the relevant Transaction_ID:

INSERT 
     INTO dbo.Result_Transaction_Mapping
        (
            fk_participant_id,
            fk_test_id,
            result_date_time,
            fk_comment_id,
            fk_review_comment_id
        )
   OUTPUT INSERTED.pk_id 
     INTO #tempRawXML(Transaction_ID)
   SELECT @INT_ParticipantID,
          QCTest_ID,
          Transaction_DateTime,
          StandardComment_ID,
          ReviewComment_ID
     FROM #tempRawXML

Is there a way I can modify the above so that instead of inserting new rows containing only the generated Transaction_ID, it updates the existing row in #tempRawXML?

Answer 1

After researching for a way to UPDATE the original tempRawXML table - to no avail - I have a solution for the initial problem using a combination of:

XML used:

declare @XML_Results XML = '
<roots>
  <root>
    <ID>-2</ID>
    <ResultDateTime>2016-08-24T10:44:22.829Z</ResultDateTime>
    <Test>
      <ID>5</ID>
      <ParticipantID>0</ParticipantID>
      <Instrument />
      <ControlSet />
      <Assay />
      <CreationDate>0001-01-01T00:00:00Z</CreationDate>
      <StartDate>0001-01-01T00:00:00Z</StartDate>
      <EndDate>0001-01-01T00:00:00Z</EndDate>
      <Closed>false</Closed>
      <SlideGenNumber>0</SlideGenNumber>
    </Test>
    <EnteredByInitials />
    <ControlSetLots />        
    <LotResults>
      <ID>13</ID>
      <LotNumber />
      <LotName />
      <ExpiryDate>0001-01-01T00:00:00Z</ExpiryDate>
      <Result>
        <ID />
        <Count>5</Count>
        <Mean>6</Mean>
        <SD>7</SD>
      </Result>
      <ParticipantID>0</ParticipantID>
      <ApprovalStatus>false</ApprovalStatus>
      <LotAnalytes />
      <LotInstruments />
      <TestDetails />
    </LotResults>
    <LotResults>
      <ID>14</ID>
      <LotNumber />
      <LotName />
      <ExpiryDate>0001-01-01T00:00:00Z</ExpiryDate>
      <Result>
        <ID />
        <Count>1</Count>
        <Mean>0</Mean>
        <SD>0</SD>
      </Result>
      <ParticipantID>0</ParticipantID>
      <ApprovalStatus>false</ApprovalStatus>
      <LotAnalytes />
      <LotInstruments />
      <TestDetails />
    </LotResults>
    <LotResults>
      <ID>0</ID>
      <LotNumber />
      <LotName />
      <ExpiryDate>0001-01-01T00:00:00Z</ExpiryDate>
      <Result>
        <ID />
        <Count>1</Count>
        <Mean>0</Mean>
        <SD>0</SD>
      </Result>
      <ParticipantID>0</ParticipantID>
      <ApprovalStatus>false</ApprovalStatus>
      <LotAnalytes />
      <LotInstruments />
      <TestDetails />
    </LotResults>
    <StandardComment>
      <ID />
      <EnteredBy />
      <Description />
    </StandardComment>
    <ReviewComment>
      <ID />
      <EnteredBy />
      <Description />
    </ReviewComment>
  </root>
  <root>
    <ID>-1</ID>
    <ResultDateTime>2016-08-24T10:44:22.829Z</ResultDateTime>
    <Test>
      <ID>5</ID>
      <ParticipantID>0</ParticipantID>
      <Instrument />
      <ControlSet />
      <Assay />
      <CreationDate>0001-01-01T00:00:00Z</CreationDate>
      <StartDate>0001-01-01T00:00:00Z</StartDate>
      <EndDate>0001-01-01T00:00:00Z</EndDate>
      <Closed>false</Closed>
      <SlideGenNumber>0</SlideGenNumber>
    </Test>
    <EnteredByInitials />
    <ControlSetLots />        
    <LotResults>
      <ID>13</ID>
      <LotNumber />
      <LotName />
      <ExpiryDate>0001-01-01T00:00:00Z</ExpiryDate>
      <Result>
        <ID />
        <Count>1</Count>
        <Mean>0</Mean>
        <SD>0</SD>
      </Result>
      <ParticipantID>0</ParticipantID>
      <ApprovalStatus>false</ApprovalStatus>
      <LotAnalytes />
      <LotInstruments />
      <TestDetails />
    </LotResults>
    <LotResults>
      <ID>14</ID>
      <LotNumber />
      <LotName />
      <ExpiryDate>0001-01-01T00:00:00Z</ExpiryDate>
      <Result>
        <ID />
        <Count>1</Count>
        <Mean>2</Mean>
        <SD>3</SD>
      </Result>
      <ParticipantID>0</ParticipantID>
      <ApprovalStatus>false</ApprovalStatus>
      <LotAnalytes />
      <LotInstruments />
      <TestDetails />
    </LotResults>
    <LotResults>
      <ID>0</ID>
      <LotNumber />
      <LotName />
      <ExpiryDate>0001-01-01T00:00:00Z</ExpiryDate>
      <Result>
        <ID />
        <Count>1</Count>
        <Mean>0</Mean>
        <SD>0</SD>
      </Result>
      <ParticipantID>0</ParticipantID>
      <ApprovalStatus>false</ApprovalStatus>
      <LotAnalytes />
      <LotInstruments />
      <TestDetails />
    </LotResults>
    <StandardComment>
      <ID />
      <EnteredBy />
      <Description />
    </StandardComment>
    <ReviewComment>
      <ID />
      <EnteredBy />
      <Description />
    </ReviewComment>
  </root>
</roots>
'

1) An additional temporary table for 'mapping' UI to IDENTITY generated IDs (thanks to @Pawel for the suggestion to get me on the right track). NOTE: I am sending an incremental negative value from the UI for the Old_ID field to ensure that these values can never match up with an existing IDENTITY.

  -- Hold mappings between old and processed IDs
  -- Used when inserting into relevant lot tables following initial top level transaction insert
  CREATE 
   TABLE #Processed_Transactions 
       (
        Old_ID INT, -- ID supplied by UI (using a negative number to ensure no conflict with IDs from Result_Transaction_Mapping table)
        ProcessedTransaction_ID INT -- ID generated during initial insert into Result_Transaction_Mapping table
       )

2) MERGE combined with OUTPUT to insert the initial transaction into the and track the Old_ID / ProcessedTransaction_ID fields in the mapping temporary table. A 1=0 scenario is raised at this point to ensure the INSERT is always triggered. This seems a little iffy but seems to be widely used.

Example from another question using MERGE instead of INSERT

   -- Function to insert the top level Result Transaction
   -- Required to populate OUTPUT variable in Processed_Transactions temporary table
   MERGE dbo.Result_Transaction_Mapping AS RTM 
   USING 
       (
          -- Extracts distinct UI assigned IDs and column information
          SELECT DISTINCT Assigned_ID, 
                 MAX(Transaction_DateTime) AS Transaction_DateTime, 
                 MAX(QCTest_ID) as QCTest_ID, 
                 MAX(StandardComment_ID) AS StandardComment_ID, 
                 MAX(ReviewComment_ID) AS ReviewComment_ID, 
                 MAX(Result_Count) AS Result_Count, 
                 MAX(Result_Mean) AS Result_Mean, 
                 MAX(Result_SD) AS Result_SD 
            FROM #tempRawXML 
           GROUP 
              BY Assigned_ID
      ) AS TR
     -- Create 1 = 0 scenario to ensure the IDs never match up to what currently exists in the Result_Transaction_Mapping table
     ON TR.Assigned_ID = RTM.pk_id
   WHEN NOT MATCHED 
    -- Ensure at least one of the transaction result columns contain a value
    -- This will also be verified on the UI
    AND TR.Result_Count > 0
    AND TR.Result_Mean > 0.0
    AND TR.Result_SD > 0.0
   THEN
 INSERT 
      (
        fk_participant_id, 
        fk_test_id, 
        result_date_time, 
        fk_comment_id, 
        fk_review_comment_id
      )
 VALUES 
      (
        @INT_ParticipantID, 
        TR.QCTest_ID, 
        TR.Transaction_DateTime, 
        TR.StandardComment_ID, 
        TR.ReviewComment_ID
      )   
 -- Following insert of a result, populate the INSERTED primary key field into the mappings table
 OUTPUT TR.Assigned_ID, 
        INSERTED.pk_id
   INTO #Processed_Transactions 
      (
        Old_ID, 
        ProcessedTransaction_ID
      );

Following this, I now have a combination of datasets which can be used to insert into the relevant Lot tables.

#tempRawXML table

ID mappings with UI negative mappings and IDENTITY IDs generated by the table

Which leads me to another predicament - the use of CURSORS and thus venturing back into the "dark acres of procedural approaches" (strongly advised against by @Shnugo in a previous question who I would imagine is 'curs'ing my name right about now.

Following a successful top-level result transaction INSERT and using the raw XML and the generated IDs above, I need to insert the remainder of the 'result specific' information to their own respective tables, the names of which have yet to be determined based on the result LotID. I have therefore setup the following combination of procedural, set based, dynamic SQL (if there is such a thing) to accomplish this:

 -- recursively access each associated Lot table based on associated Lot ID's
 DECLARE @LotNumber NVARCHAR(20), @LotID INT 
 -- Querystring to hold all set update calls
 DECLARE @ResultQueryString NVARCHAR(MAX) = ''
 DECLARE Lot_Cursor
  CURSOR FAST_FORWARD
     FOR
      -- Select the lot numbers based on the available IDs
      SELECT 
    DISTINCT L.pk_id AS LotID,
             L.number AS LotNumber
        FROM dbo.Lot L
        LEFT
        JOIN #tempRawXML TR 
          ON TR.Lot_ID = L.pk_id
       WHERE L.pk_id IN (TR.Lot_ID)

        OPEN Lot_Cursor

       FETCH 
        NEXT 
        FROM Lot_Cursor
        INTO @LotID, @LotNumber

       WHILE @@fetch_status = 0
       BEGIN
         SET @ResultQueryString +=
           N'  MERGE dbo.[' + @LotNumber + '] AS L
               USING 
                    (
                        SELECT PT.ProcessedTransaction_ID,
                               TR.Result_ID,
                               TR.Result_Count, 
                               TR.Result_Mean, 
                               TR.Result_SD 
                          FROM #tempRawXML TR
                          JOIN #Processed_Transactions PT
                            ON PT.Old_ID = TR.Assigned_ID
                         WHERE TR.Lot_ID = '+ CAST(@LotID AS NVARCHAR(20)) +'
                    ) R
                 ON R.Result_ID = L.pk_id   
               WHEN NOT MATCHED     
                AND R.Result_Count > 0
                AND R.Result_Mean > 0.0
                AND R.Result_SD > 0.0       
               THEN
             INSERT
                  (
                     fk_result_transaction_mapping_id,
                     count,
                     mean,
                     standard_deviation,
                     result_status              
                  )
             VALUES
                  (
                     R.ProcessedTransaction_ID,
                     R.Result_Count,
                     R.Result_Mean,
                     R.Result_SD,
                     1
                  ); '            

       FETCH
        NEXT
        FROM Lot_Cursor
        INTO @LotID, @LotNumber

         END
       CLOSE Lot_Cursor
  DEALLOCATE Lot_Cursor        

        -- @Processed_Transactions temp table variable must be declared when executing dynamic sql
        --EXEC sp_executesql @ResultQueryString, N'@Processed_Transactions MyTable READONLY', @Processed_Transactions=@Processed_Transactions
        EXEC (@ResultQueryString)

My follow-up question here - is this an acceptable use of CURSORS (bearing in mind that there can only be a maximum of 6 iterations)? Additionally, is there a way I can avoid the use of CURSORs in this scenario?

Answer 2

Your question and your answer is quite a lot to read...

I want to offer you a very reduced MCVE (minimal, complete, verifiable example) to boild down your needs to the actual problem - as far as I understand it...

The following solution has one tiny need: The table with the IDENTITY ID must have a column for temporary storage of an external ID. If this is possible, you could use this much simpler approach:

--This table must have a column for temporary storage of the external ID
DECLARE @TableWithExistingData TABLE(ID INT IDENTITY,SomeData VARCHAR(100),ExternalID INT);
INSERT INTO @TableWithExistingData(SomeData) VALUES
('Data for ID=1'),('Data for ID=2');

--This is the existing data
SELECT * FROM @TableWithExistingData

--This is the derived table from your XML. 
--You can use ROW_NUMBER() to create a running number on the fly.
--Use this as the rows temporary ID
--These new rows should be inserted in the table with existing data
--DataForOtherTable should be inserted in another table but with the newly created ID as FK
DECLARE @NewRows TABLE(ID INT,SomeNewData VARCHAR(100),DataForOtherTable VARCHAR(100));
INSERT INTO @NewRows(ID,SomeNewData,DataForOtherTable) VALUES
(1,'New value 1','More data 1'),(2,'New value 2','More data 2');

--This table will hold the newly created ID and the external ID
DECLARE @Mapping TABLE(nwID INT,extID INT);

--OUTPUT is great but can only return columns of the target table, 
--hence the need to have the external ID within your table
INSERT INTO @TableWithExistingData(SomeData,ExternalID)
OUTPUT inserted.ID,inserted.ExternalID INTO @Mapping
SELECT nr.SomeNewData,nr.ID 
FROM @NewRows AS nr;

--This is your other existing table, where you want to store values with the new ID as FK
DECLARE @SideTable TABLE(NewlyCreatedID INT,AndMoreDataForOtherTable VARCHAR(100));

--use the mapping table to get the ID into the table
INSERT INTO @SideTable
SELECT nwID,nr.DataForOtherTable 
FROM @Mapping AS m
INNER JOIN @NewRows AS nr ON m.extID=nr.ID

--And this is the result in all tables
SELECT * FROM @NewRows
SELECT * FROM @TableWithExistingData
SELECT * FROM @SideTable;

One point to consider: If you use ROW_NUMBER and there is the same process happening in the same seconds, you might mix your external ID with the other process... You could use GUIDs or concatenate the ROW_NUMBER with a unique sessionID or whatever you can use there...