model name更改为不允许重复, 原来重复的model name则需要提供sql script处理(可加后缀"_"區分)

最新推荐文章于 2025-09-28 15:16:22 发布

转载最新推荐文章于 2025-09-28 15:16:22 发布 · 2.3k 阅读

本文介绍了一个用于解决 SQL Server 中重复 ModelName 的存储过程。该过程通过在重复的 ModelName 后添加序号的方式进行区分，使用了游标及计数器实现逐条更新。

在SQL server里时不区分大小写的

ItemID ModelName Status --------想整成这样------ItemID ModelName Status

1 marytest testing 1 marytest testing

2 MaryTest testing 2 MaryTest _1 testing

3 mm testing 3 mm testing

4 MARYTEST testing 4 MARYTEST_2 testing

SO 我写了一个存储过程来执行

游标跳过第一行，对第二行进行处理。故用了两次FETCH NEXT

CREATE PROCEDURE UpdateBMSITEMNAME
AS
BEGIN
DECLARE @item_id   int
DECLARE @item_name nvarchar(500)
DECLARE @qty       int
DECLARE @no        int
DECLARE main_cursor CURSOR FOR
		SELECT MODELNAME,COUNT(*) AS QTY FROM BMSItem GROUP BY MODELNAME HAVING COUNT(*)>1
OPEN main_cursor
FETCH NEXT FROM main_cursor INTO @item_name,@qty
WHILE ((@@FETCH_STATUS) =0)
BEGIN
	SET @no=0
	DECLARE line_cursor CURSOR FOR
		SELECT ITEMID FROM BMSITEM WHERE MODELNAME=@item_name
	OPEN line_cursor
	FETCH NEXT FROM line_cursor INTO @item_id
	FETCH NEXT FROM line_cursor INTO @item_id
	WHILE ((@@FETCH_STATUS)=0)
	BEGIN
		print 'item_id'
		print @item_id
		set @no = @no + 1
		UPDATE BMSITEM SET MODELNAME=MODELNAME+'_'+CONVERT(NVARCHAR(50),@no) WHERE ITEMID=@item_id
		FETCH NEXT FROM line_cursor INTO @item_id
	END
	CLOSE line_cursor
	DEALLOCATE line_cursor

	FETCH NEXT FROM main_cursor INTO @item_name,@qty
END
CLOSE main_cursor
DEALLOCATE main_cursor
END