views:

23

answers:

0

Below in a query which inserts records to SimpleDictationProfileMapping table after reading it from a XML string. Now this query inserts a single record in which DictationCaptureProfileID is @dictationCaptureProfileId . Now i want to insert multiple rows in which @dictationCaptureProfileId is different and other 2 values are same.

What i want to achieve by this is in case parent changes all child values should also change.

    INSERT  INTO SimpleDictationProfileMapping
    (
      DictationCaptureProfileID,
      DictationProfileMappingAttributeID,
      DictationProfileMappingAttributeValue
     )
     SELECT  @dictationCaptureProfileId,
     row.value('@attrId','varchar(max)'),
     row.value('@value', 'varchar(max)')
     FROM    @simpleDictationCaptureProfileMappings.nodes('/simpleMappingAtribute/attribute')
    AS d ( row ) ;           

I want

INSERT  INTO SimpleDictationProfileMapping
  (
    DictationCaptureProfileID OR (SELECT  DictationCaptureProfileID
    FROM    DictationCaptureProfile
    WHERE   SystemDictationCaptureProfileID = @systemDictationCaptureProfileID),
    DictationProfileMappingAttributeID,
    DictationProfileMappingAttributeValue
  )
SELECT  @dictationCaptureProfileId ,
row.value('@attrId','varchar(max)'),
row.value('@value', 'varchar(max)')
FROM @simpleDictationCaptureProfileMappings.nodes ('/simpleMappingAtribute/attribute')
AS d ( row ) ;

Please tell how to achieve this.

related questions