与Oracle中的批量收集等效的SQL Server [英] SQL Server Equivalent of Bulk Collect in Oracle
本文介绍了与Oracle中的批量收集等效的SQL Server的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想在SQL Server中复制以下代码.我四处看看,看来唯一的选择就是只使用一个直线光标,但是有没有更好的方法:
I want to replicate the following code in SQL Server. I've had a look around and it seems like the only option is to just use a straight cursor, but is there a better way:
DECLARE
CURSOR cursor1 IS
SELECT *
FROM table1;
TYPE cursor_aat IS TABLE OF cursor1%ROWTYPE;
l_cursor cursor_aat;
BEGIN
OPEN cursor1;
LOOP
FETCH cursor1
BULK COLLECT INTO l_cursor LIMIT 200;
FOR INDX IN 1 .. l_cursor.COUNT LOOP
IF (CheckSomeData() = 0) THEN
INSERT INTO new_table
(col1, col2)
values
(l_cursor(INDX).col1, l_cursor(INDX).col2);
INSERT INTO new_table2
...
end if
END LOOP;
EXIT WHEN l_cursor.COUNT < 200;
...
推荐答案
如果要一次执行200行,则可以使用临时表和游标.
You could use a temporary table and a cursor if you want to do 200 rows at once.
这篇关于与Oracle中的批量收集等效的SQL Server的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文