This should work especially if you're not bothered about the order of the insert for Table 1

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)

You might also want to consider changing the substring to be SUBSTRING(C_CODE,3,LEN(C_CODE)-2)) otherwise you'll have a problem when you get to 100, as you will pick up 10, so if you have 9 rows to insert the numbers will be in the range 11-20. so 100 will always be the max.