sql serveri不用游标，适合于循环DML (update delete insert)的场合实例

sql serveri不用游标，适合于循环DML (update delete insert)的场合实例

 
		use tempdb
		go
		if exists(select * from tempdb..sysobjects where id=object_id('tempdb..#tbl_Cursor'))
			DROP TABLE #tbl_Cursor
		if exists(select * from tempdb..sysobjects where id=object_id('tempdb..#tbl_Value'))
			DROP TABLE #tbl_Value
		go
		if object_id(N'testdata')is not null
		drop table testdata
		
		SELECT row_number()over(ORDER BY a.storeid) ID,* INTO testdata
		FROM  sys.tables
		
		
		SELECT row_number()over(ORDER BY a.storeid) IDD,* INTO #tbl_Cursor
		FROM  testdata where id%3=0
		set @tbl_rows=@@rowcount
		CREATE CLUSTERED INDEX cix_tbl_tmp ON #tbl_Cursor(id)
		
		SELECT * INTO #tbl_Value FROM #tbl_Cursor WHERE 1=2
		WHILE @tbl_rows>0
		BEGIN
			TRUNCATE TABLE #tbl_Value
			DELETE FROM #tbl_Cursor output deleted.* into #tbl_Value WHERE id=@tbl_rows
			--
			select * from testdata
			update top  T1 set id=id+10000 from  testdata t1 inner join #tbl_Value on #tbl_Value .id=t1.id
			select * from testdata 
			deleted top T1 from testdata t1 inner join #tbl_Value on #tbl_Value .id=t1.id
			select * from testdata 
			insert into testdata select *,........  from #tbl_Value
			select * from testdata			
			SET @tbl_rows=@tbl_rows-1					
		END