Steven Willis (3/7/2013)

---

Just to add another option with a slightly more complex query that doesn't require a seed value. Also, by adding identity columns it will keep the rows in the same order.

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