Thank you for getting back to me on this.

I made a slight change to the code below and added a while loop to split the comma separated list up, and pass each value to the transaction below it. Would something that simple work?

SET XACT_ABORT ON -- Ensures that transactions are rolled back automatically on errors

DECLARE @Dry_Run int = 1 -- 0 to commit, 1 to rollback

DECLARE @CopyTBFromAA varchar(50)

DECLARE @CopyTBToAA varchar(50)

SET @CopyTBFromAA='Name1 IP/OP'

--Added code to split comma separated list of AssigningAuthorities, and pass one AssigningAuthority at a time to old code.

DECLARE @comma VARCHAR(MAX) = 'NAME1,NAME2,NAME3,'

DECLARE @begin INT = 0, @count INT = 0, @max-2 INT

SELECT @max-2 = LEN(@comma) - LEN(REPLACE(@comma,',','')

WHILE @count < @max-2

BEGIN

SET @CopyTBToAA=(SELECT SUBSTRING(@comma,@begin,CHARINDEX(',',@comma,@begin+1)-@begin))--Copy thumbprint to AssigningAuthority

--SELECT (CHARINDEX(',',@comma,@begin+1)+1)

SET @begin = (CHARINDEX(',',@comma,@begin+1)+1)

SET @count = @count + 1

END

--End

Declare @CopyFromVIEGuid varchar(50)

Declare @CopyToVIEGuid varchar(50)

Select @CopyFromVIEGuid = VIEGuid From [HEALINX].[dbo].AssigningAuthority Where Name=@CopyTBFromAA

Select @CopyToVIEGuid = VIEGuid From [HEALINX].[dbo].AssigningAuthority Where Name=@CopyTBToAA

BEGIN TRANSACTION

IF (len(@CopyFromVIEGuid) > 0 AND len(@CopyToVIEGuid) > 0)

BEGIN

IF (NOT EXISTS(Select Null From [INTEROP].[dbo].XdsInboundSecurity Where AssigningAuthorityVIEGuid=@CopyToVIEGuid) )

BEGIN

INSERT INTO [INTEROP].[dbo].XdsInboundSecurity

(AssigningAuthorityVIEGuid, ClientCertificateThumbprint, ClientCertificateExpirationDate, IsActive,CreatedByVIEGuid,CreatedDate,ModifiedByVIEGuid,ModifiedDate,IsDeleted)

SELECT @CopyToVIEGuid, ClientCertificateThumbprint, ClientCertificateExpirationDate, IsActive,CreatedByVIEGuid,CreatedDate,ModifiedByVIEGuid,ModifiedDate,IsDeleted

FROM [INTEROP].[dbo].XdsInboundSecurity

WHERE AssigningAuthorityVIEGuid= @CopyFromVIEGuid

IF @@ERROR <> 0 GOTO ERROR_HANDLER

END

ELSE

BEGIN

UPDATE [INTEROP].[dbo].XdsInboundSecurity

SET ClientCertificateThumbprint = (Select ClientCertificateThumbprint From [INTEROP].[dbo].XdsInboundSecurity Where AssigningAuthorityVIEGuid=@CopyFromVIEGuid)

WHERE AssigningAuthorityVIEGuid= @CopyToVIEGuid

IF @@ERROR <> 0 GOTO ERROR_HANDLER

END

END

ELSE

BEGIN

PRINT 'Given Assigning Authority information not found in the system'

GOTO AFTER_VALUES

END

IF @Dry_Run = 1 GOTO DRY_RUN

COMMIT TRANSACTION

SELECT 'Success'

GOTO AFTER_VALUES

ERROR_HANDLER:

ROLLBACK TRANSACTION

PRINT 'An error has occured; the changes have been rolled back.'

GOTO AFTER_VALUES

DRY_RUN:

SELECT * FROM [INTEROP].[dbo].XdsInboundSecurity Where AssigningAuthorityVIEGuid=@CopyToVIEGuid

ROLLBACK TRANSACTION

SELECT 'Dry Run'

GOTO AFTER_VALUES

AFTER_VALUES:

SELECT 'AFTER TRANSACTION'

SELECT * FROM [INTEROP].[dbo].XdsInboundSecurity Where AssigningAuthorityVIEGuid=@CopyToVIEGuid

SET XACT_ABORT OFF -- Reverting back to the default