也可以自动生成 ALTER TABLE T1 ADD IDX INT INDENTITY(1010001,1) GO UPDATE T1 SET ID=CONVERT(CHAR(10),IDX) GO ALTER TABLE T1 DROP COLUMN IDX GO
select name,IDENTITY(int,1,1) as Id into #Temp from 商品表update 商品表 set id='101'+case when t.id<10 then '000'+cast(t.id as char(1)) when t.id>=10 and t.id<100 then '00'+cast(t.id as char(2)) when t.id>=100 and t.id<1000 then '0'+cast(t.id as char(3)) when t.id>=1000 and t.id<10000 then cast(t.id as char(4)) end from 商品表 a,#Temp t where a.name=t.name
--無規則生成IDUPDATE YourTableName SET ID=LEFT(NEWID(),10) WHERE ID IS NULL
ALTER TABLE T1 ADD IDX INT INDENTITY(1010001,1)
GO
UPDATE T1
SET ID=CONVERT(CHAR(10),IDX)
GO
ALTER TABLE T1 DROP COLUMN IDX
GO
set id='101'+case when t.id<10 then '000'+cast(t.id as char(1))
when t.id>=10 and t.id<100 then '00'+cast(t.id as char(2))
when t.id>=100 and t.id<1000 then '0'+cast(t.id as char(3))
when t.id>=1000 and t.id<10000 then cast(t.id as char(4))
end
from 商品表 a,#Temp t
where a.name=t.name
SET ID=LEFT(NEWID(),10)
WHERE ID IS NULL