DECLARE @TEST1 TABLE (C_NAME varchar(10), C_CODE VARCHAR(5))
INSERT INTO @TEST1(C_NAME,C_CODE) VALUES('John', 'CL1')
INSERT INTO @TEST1(C_NAME,C_CODE) VALUES('Jake', 'CL2')
INSERT INTO @TEST1(C_NAME,C_CODE) VALUES('Joe', 'CL3')
INSERT INTO @TEST1(C_NAME,C_CODE) VALUES('Jane', 'CL4')


DECLARE @TEST2 TABLE (P_NAME varchar(10), P_CODE VARCHAR(5))
INSERT INTO @TEST2(P_NAME,P_CODE) VALUES('Ray', 'PL91')
INSERT INTO @TEST2(P_NAME,P_CODE) VALUES('James', 'PL92')
INSERT INTO @TEST2(P_NAME,P_CODE) VALUES('Fred', 'PL93')
INSERT INTO @TEST2(P_NAME,P_CODE) VALUES('Mac', 'PL94')


DECLARE @x INT
SELECT @x = MAX(CONVERT(INT,SUBSTRING(C_CODE,3,2))) FROM @TEST1

INSERT INTO @TEST1(C_NAME, C_CODE) 
 (SELECT P_NAME,
	C_CODE = 'CL' + CONVERT(VARCHAR(5),@x+1)
	FROM @TEST2) 

C_NAME	C_CODE
----------------------
John	        CL1
Jake	        CL2
Joe	        CL3
Jane	        CL4
Ray	        CL5
James       CL5
Fred	        CL5
Mac	        CL5

C_NAME	C_CODE
----------------------
John	        CL1
Jake	        CL2
Joe	        CL3
Jane	        CL4
Ray	        CL5
James       CL6
Fred	        CL7
Mac	        CL8

INSERT INTO @TEST1(C_NAME, C_CODE) 
 (SELECT P_NAME,
	C_CODE = 'CL' + CONVERT(VARCHAR(5),@x+ROW_NUMBER() OVER (ORDER BY PCODE))
	FROM @TEST2) 

IF OBJECT_ID('tempdb..#Test1') IS NOT NULL
DROP TABLE #Test1
IF OBJECT_ID('tempdb..#Test2') IS NOT NULL
DROP TABLE #Test2

CREATE TABLE #Test1 (ID INT IDENTITY(1,1),C_NAME VARCHAR(20),C_CODE VARCHAR(10),PRIMARY KEY(ID))
INSERT  INTO #Test1 (C_NAME,C_CODE) VALUES ('John','CL1')
INSERT  INTO #Test1 (C_NAME,C_CODE) VALUES ('Jake','CL2')
INSERT  INTO #Test1 (C_NAME,C_CODE) VALUES ('Joe','CL3')
INSERT  INTO #Test1 (C_NAME,C_CODE) VALUES ('Jane','CL4')

CREATE TABLE #Test2 (ID INT IDENTITY(1,1),P_NAME VARCHAR(20),P_CODE VARCHAR(10),PRIMARY KEY(ID))
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('Ray', 'PL91')
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('James', 'PL92')
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('Fred', 'PL93')
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('Mac', 'PL94')
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('Martin', 'X911')
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('George', 'X922')
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('Jon', 'ABCDE99993')
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('Mary', 'BBB4')
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('Carl', 'DF691')
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('Mackenzie', 'FGL9')
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('Sally', 'QW9883')
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('Karen', 'OPP')
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('Marty', 'ZZ11')
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('Abram', 'X122')
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('Zonnie', 'QWERTY9993')
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('Ryan', 'DCDCDC4')

;WITH cte AS
(    
SELECT 
    R1.[C_NAME]
   ,'CL'+CAST(ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY R1.ID)+[MAX_C_CODE] AS VARCHAR(20)) AS [NEW_C_CODE]
FROM
    (
    SELECT DISTINCT
        P1.ID
       ,P1.[P_NAME] AS [C_NAME]
       ,STUFF(MAX(C1.[C_CODE]) OVER (PARTITION BY 1),1,2,'') AS [MAX_C_CODE]
    FROM
        #Test1 C1
    CROSS APPLY
        #Test2 P1
    WHERE
        P1.ID > C1.ID
    ) R1
)       
INSERT INTO #Test1
SELECT 
    C_NAME
   ,NEW_C_CODE
FROM 
    cte


SELECT * FROM #Test1