游标用来循环数据比较方便。
DECLARE my_cursor CURSOR FOR
SELECT id FROM [db] WHERE Code IS null
OPEN my_cursor
declare @newid varchar(max)
FETCH NEXT FROM my_cursor INTO @newid
WHILE @@fetch_status=0
BEGIN
update [db] set code='newdata'where id=@newid
FETCH NEXT FROM my_cursor INTO @newid
END
CLOSE my_cursor
DEALLOCATE my_cursor
GO
触发器,在生成自动的列数据时用触发器比较方便,此处只写一个插入的触发器实例:
create trigger TableOnInsert
on [db]
for insert
as
<span> </span>declare @newid varchar(max)
<span> </span>declare @tempnewid varchar(50)
select @newid +=CAST(id as VARCHAR(50))+',' from inserted
WHILE(CHARINDEX(',',@newid)>0)
BEGIN
SET @tempnewid=SUBSTRING(@newid,0,CHARINDEX(',',@newid))
<span> </span>SET @newid=SUBSTRING(@newid,CHARINDEX(',',@newid)+1,LEN(@newid))
END
go