Mise à jour de la table temporaire existante avec l'ID créé pendant l'insertion basée sur l'set

Après avoir recherché un moyen de mettre à tempRawXML table tempRawXML originale – en vain – j'ai une solution pour le problème initial en utilisant une combinaison de:

XML utilisé:

 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) Une table temporaire supplémentaire pour «mapper» l'interface user aux ID générés IDENTITY (merci à @Pawel pour la suggestion de me mettre sur la bonne voie). REMARQUE: Old_ID une valeur incrémentielle négative à partir de l'interface user pour le champ Old_ID afin de garantir que ces valeurs ne correspondent jamais à une IDENTITY existante.

  -- 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 combiné avec OUTPUT pour insert la transaction initiale dans le et suivre les champs Old_ID / ProcessedTransaction_ID dans la table temporaire de mappage. Un scénario 1 = 0 est levé à ce stade pour s'assurer que l' INSERT est toujours déclenché. Cela semble un peu incertain mais semble être largement utilisé.

Exemple d'une autre question utilisant MERGE au lieu de 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 ); 

Suite à cela, j'ai maintenant une combinaison d'sets de données qui peuvent être utilisés pour insert dans les tables de Lot pertinentes.

Table #tempRawXML

ID mappings avec ID mappings d'interface user négative et ID d' IDENTITY générés par la table

Ce qui m'amène à une autre situation difficile – l'utilisation de CURSORS et donc revenir dans les "acres obscures des approches procédurales" (fortement déconseillé par @Shnugo dans une question précédente qui, je suppose, est en train de balancer mon nom.

Après une transaction de résultat de haut niveau INSERT réussie et en utilisant le XML brut et les ID générés ci-dessus, j'ai besoin d'insert le rest de l'information 'spécifique au résultat' à leurs propres tables respectives, dont les noms doivent encore être déterminés sur la base le résultat LotID . J'ai donc mis en place la combinaison suivante de SQL dynamic, paramétré et basé sur des procédures (s'il y a une telle chose) pour accomplir ceci:

  -- recursively access each associated Lot table based on associated Lot ID's DECLARE @LotNumber NVARCHAR(20), @LotID INT -- Queryssortingng to hold all set update calls DECLARE @ResultQuerySsortingng 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 @ResultQuerySsortingng += 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 @ResultQuerySsortingng, N'@Processed_Transactions MyTable READONLY', @Processed_Transactions=@Processed_Transactions EXEC (@ResultQuerySsortingng) 

Ma question de suivi ici – est-ce une utilisation acceptable de CURSORS (en gardant à l'esprit qu'il ne peut y avoir qu'un maximum de 6 itérations)? De plus, y a-t-il un moyen d'éviter l'utilisation de CURSOR dans ce scénario?

Votre question et votre réponse sont nombreuses à lire …

Je veux vous offrir un MCVE très réduit (exemple minimal, complet, vérifiable) pour réduire vos besoins au problème réel – autant que je sache …

La solution suivante a un besoin minime: La table avec ID IDENTITY doit avoir une colonne pour le stockage temporaire d'un ID externe. Si cela est possible, vous pouvez utiliser cette approche beaucoup plus simple:

 --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; 

Un point à considérer: Si vous utilisez ROW_NUMBER et que le même process se déroule dans les mêmes secondes, vous pouvez mélanger votre ID externe avec l'autre process … Vous pouvez utiliser des GUID ou concaténer le ROW_NUMBER avec un unique sessionID ou tout ce que vous pouvez utiliser là …