Results 1 to 2 of 2

Thread: Bulk Copy

  1. #1
    Join Date
    Jan 2003
    Location
    Bangalore
    Posts
    36

    Unanswered: Bulk Copy

    I have a table with 10 millions of records. I want to copy some 20000 odd rows into a temporary table using the bulk copy option and I would like to do that from within a Stored procedure.
    What is the syntax for Bulk copy in SQL?

    Appreciate your suggestions.

  2. #2
    Join Date
    Oct 2001
    Location
    Bangalore
    Posts
    186
    Hi,

    Since you have to copy 20,000 rows from the table and insert them into temporary table, it would be better that you select rows from the table in a cursor and then fetch one at a time depending on any unique value say the PK and insert it one by one in the temp table. This way there will be no deadlock due to log space.

    Here's the syntax:

    CREATE PROCEDURE BULKLOAD ( )
    LANGUAGE SQL
    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    ------------------------------------------------------------------------
    P1: BEGIN
    DECLARE T_PKID, T_TOTAL, T_COUNTER INT;
    DECLARE BULKCUR CURSOR WITH HOLD FOR SELECT PKID FROM OWNER.TABLENAME;
    SELECT COUNT(*) INTO T_TOTAL FROM OWNER.TABLENAME; -- here you can have a where clause to just fetch 20000 rec that you require
    SET T_COUNTER = 0;
    OPEN BULKCUR;
    WHILE
    T_COUNTER < T_TOTAL DO
    FETCH BULKCUR INTO T_PKID;
    IF NOT EXISTS (SELECT 1 FROM OWNER.TEMPTABLE WHERE PKID = T_PKID ) THEN
    INSERT INTO OWNER.TEMPTABLE (PKID, COL1, COL2...)
    SELECT PKID, COL1, COL2... FROM OWNER.TABLENAME WHERE PKID = T_PKID;
    COMMIT;
    END IF;
    SET T_COUNTER = T_COUNTER+1;
    END WHILE;
    END P1

    The other option is to use db2 load utility... which is quite fast.

    cheers,
    Prashant

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •