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

浏览数：42 / 时间：2015年06月12日

 
		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

郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。

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