sql serveri不用游标，适合于循环DML (update delete insert)的场合实例
分类：TOAD FOR MS
sql serveri不用游标，适合于循环DML (update delete insert)的场合实例
use tempdbgoif exists(select * from tempdb..sysobjects where id=object_id('tempdb..#tbl_Cursor'))DROP TABLE #tbl_Cursorif exists(select * from tempdb..sysobjects where id=object_id('tempdb..#tbl_Value'))DROP TABLE #tbl_Valuegoif object_id(N'testdata')is not nulldrop table testdataSELECT row_number()over(ORDER BY a.storeid) ID,* INTO testdataFROM sys.tablesSELECT row_number()over(ORDER BY a.storeid) IDD,* INTO #tbl_CursorFROM testdata where id%3=0set @tbl_rows=@@rowcountCREATE CLUSTERED INDEX cix_tbl_tmp ON #tbl_Cursor(id)SELECT * INTO #tbl_Value FROM #tbl_Cursor WHERE 1=2WHILE @tbl_rows>0BEGINTRUNCATE TABLE #tbl_ValueDELETE FROM #tbl_Cursor output deleted.* into #tbl_Value WHERE id=@tbl_rows–select * from testdataupdate top T1 set id=id+10000 from testdata t1 inner join #tbl_Value on #tbl_Value .id=t1.idselect * from testdatadeleted top T1 from testdata t1 inner join #tbl_Value on #tbl_Value .id=t1.idselect * from testdatainsert into testdata select *,…….. from #tbl_Valueselect * from testdataSET @tbl_rows=@tbl_rows-1END
上一篇powershell array sort sum ;ls|rm;get ascii
顶0踩0
，以一种进取的和明智的方式同它们奋斗。
