SQL Server Equivalent of Bulk Collect in Oracle
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; ...
You could use a temporary table and a cursor if you want to do 200 rows at once.