--创建一个测试表
CREATE TABLE TMP
(
BH INT IDENTITY(1,1),
COLUMN1 VARCHAR(10) NULL
)

GO

--插入数据
INSERT INTO TMP (COLUMN1) VALUES ('100000')
INSERT INTO TMP (COLUMN1) VALUES ('100000')
INSERT INTO TMP (COLUMN1) VALUES ('100000')
INSERT INTO TMP (COLUMN1) VALUES ('100000')
INSERT INTO TMP (COLUMN1) VALUES ('100000')
INSERT INTO TMP (COLUMN1) VALUES ('100000')
INSERT INTO TMP (COLUMN1) VALUES ('100000')

INSERT INTO TMP (COLUMN1) VALUES ('200000')
INSERT INTO TMP (COLUMN1) VALUES ('200000')
INSERT INTO TMP (COLUMN1) VALUES ('200000')
INSERT INTO TMP (COLUMN1) VALUES ('200000')
INSERT INTO TMP (COLUMN1) VALUES ('200000')
INSERT INTO TMP (COLUMN1) VALUES ('200000')
INSERT INTO TMP (COLUMN1) VALUES ('200000')

--增加一个新字段
ALTER TABLE TMP ADD COLUMN2 VARCHAR(10) NULL


--存储过程
CREATE PROCEDURE SP_TMP
AS
BEGIN
CREATE TABLE #TMP1
(
XH INT IDENTITY,
BH INT NULL
)


DECLARE @BH INT,@VALUE VARCHAR(10),@ROWCOUNT INT
DECLARE C1 CURSOR FOR SELECT COLUMN1,COUNT(*) FROM TMP GROUP BY COLUMN1 HAVING COUNT(*)>1
OPEN C1
FETCH NEXT FROM C1 INTO @VALUE,@ROWCOUNT
WHILE @@FETCH_STATUS=0
BEGIN
TRUNCATE TABLE #TMP1
INSERT INTO #TMP1 (BH) SELECT BH FROM TMP WHERE COLUMN1=@VALUE
UPDATE TMP SET COLUMN2=COLUMN1 + RIGHT('0000'+CONVERT(VARCHAR(4),#TMP1.XH),4) FROM #TMP1 WHERE TMP.BH=#TMP1.BH


FETCH NEXT FROM C1 INTO @VALUE,@ROWCOUNT
END
CLOSE C1
DEALLOCATE C1

END

EXEC SP_TMP

SELECT COLUMN1,COLUMN2 FROM TMP

--下面是执行的结果
100000 1000000001
100000 1000000002
100000 1000000003
100000 1000000004
100000 1000000005
100000 1000000006
100000 1000000007
200000 2000000001
200000 2000000002
200000 2000000003
200000 2000000004
200000 2000000005
200000 2000000006
200000 2000000007