How to create Cursor for Insert in Sybase

Question

I have the below query that inserts data into a table. The issue is that there are a lot of records and it fills up the transaction log in the database. So, I need to either do the insert in batches or use a cursor. Anyone has idea how the below query can be re-structured to work using a cursor?

SELECT 
    h.issue_id,
    h.account_id,
    h.shares_held,
    h.shares_change,
    a.current_report_date
INTO #tmp_holding    
FROM edgar_holding h
JOIN edgar_account a ON h.account_id = a.account_id

INSERT INTO edgar_holding_hist
SELECT
    h.issue_id,
    h.account_id,
    h.shares_held,
    h.shares_change,
    h.current_report_date
FROM #tmp_holding h
LEFT JOIN edgar_holding_hist hh 
ON hh.account_id = h.account_id
AND hh.issue_id = h.issue_id
AND hh.current_report_date = h.current_report_date
WHERE hh.issue_id IS NULL
OR hh.account_id IS NULL
OR hh.current_report_date IS NULL

DROP TABLE #tmp_holding 

Answer 1

There's a couple different ways you could do this. One would be to declare the cursor on your initial query, and the other would be to declare the cursor on the #temp table.

For simplicity, I'm going to use the #temp table:

DECLARE holding_cursor FOR
SELECT
    h.issue_id,
    h.account_id,
    h.shares_held,
    h.shares_change,
    h.current_report_date
FROM #tmp_holding h
LEFT JOIN edgar_holding_hist hh
ON hh.account_id = h.account_id
AND hh.issue_id = h.issue_id
AND hh.current_report_date = h.current_report_date
WHERE hh.issue_id IS NULL
OR hh.account_id IS NULL
OR hh.current_reporting_data IS NULL

DECLARE
    @issue_id [insert datatype here],
    @account_id [insert datatype here],
    @shares_held [insert datatype here],
    @shares_change [insert datatype here],
    @current_report_date [insert datatype here]

OPEN holding_cursor
fetch holding_cursor into @issue_id, @account_id, @shares_held, @shares_change, @current_report_date
WHILE (@@sqlstatus = 0)
BEGIN
    INSERT INTO edgar_holding_hist (issue_id, account_id, shares_held, shares_change, current_report_date)
    VALUES (@issue_id, @account_id, @shares_held, @shares_change, @current_report_date)

FETCH holding_cursor into @issue_id, @account_id, @shares_held, @shares_change, @current_report_date
END

CLOSE holding_cursor
DEALLOCATE holding_cursor

DROP TABLE #tmp_holding

Something like that should work. Since you are also worried about the transaction log, you can use an if statement to issue a dump tran every so often using @@rowcount, which counts the number of rows fetched since the cursor was opened, as a counter.