CODEWITHSUNDEEP
oracle-databaseplsqloracle-sqldeveloperoracle11gr2
Cronas De Se
Cronas De Se

Reputation: 331

Insert into select statement on the same table

I'm currently migrating data from legacy system to the current system.

I have this INSERT statement inside a stored procedure.

INSERT INTO TABLE_1
(PRIMARY_ID, SEQUENCE_ID, DESCR)
SELECT LEGACY_ID PRIMARY_ID
 , (SELECT COUNT(*) + 1
    FROM TABLE_1 T1
    WHERE T1.PRIMARY_ID = L1.LEGACY_ID) SEQUENCE_ID
 , L1.DESCR
FROM LEGACY_TABLE L1;

However, whenever I have multiple values of LEGACY_ID from LEGACY_TABLE, the query for the SEQUENCE_ID doesn't increment.

Why is this so? I can't seem to find any documentation on how the INSERT INTO SELECT statement works behind the scenes. I am guessing that it selects all the values from the table you are selecting and then inserts them simultaneously after, that's why it doesn't increment the COUNT(*) value?

What other workarounds can I do? I cannot create a SEQUENCE because the SEQUENCE_ID must be based on the number of PRIMARY_ID that are present. They are both primary ids.

Thanks in advance.

Upvotes: 1

Views: 9571

Answers (1)

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

Yes, The SELECT will be executed FIRST and only then the INSERT happens.

A Simple PL/SQL block below, will be a simpler approach, though not efficient.

DECLARE
    V_SEQUENCE_ID NUMBER;
    V_COMMIT_LIMIT:= 20000;
    V_ITEM_COUNT := 0;
BEGIN
    FOR REC IN (SELECT LEGACY_ID,DESCR FROM LEGACY_TABLE)
    LOOP
        V_SEQUENCE_ID:= 0;

        SELECT COUNT(*)+1 INTO V_SEQUENCE_ID FROM TABLE_1 T1
         WHERE T1.PRIMARY_ID = REC.LEGACY_ID


        INSERT INTO TABLE_1
        (PRIMARY_ID, SEQUENCE_ID, DESCR)
        VALUES
        (REC.LEGACY_ID,V_SEQUENCE_ID,REC.DESCR);

        V_ITEM_COUNT := V_ITEM_COUNT + 1;

        IF(V_ITEM_COUNT >= V_COMMIT_LIMIT)
        THEN
             COMMIT;
             V_ITEM_COUNT := 0;
        END IF;

    END LOOP;
    COMMIT;
END;
/

EDIT: Using CTE:

WITH TABLE_1_PRIM_CT(PRIMARY_ID, SEQUENCE_ID) AS
(
    SELECT L1.LEGACY_ID,COUNT(*) 
    FROM LEGACY_TABLE L1 
      LEFT OUTER JOIN TABLE_1 T1 
        ON(L1.LEGACY_ID = T1.PRIMARY_ID)
    GROUP BY L1.LEGACY_ID
)
INSERT INTO TABLE_1
(SELECT L1.LEGACY_ID,
       CTE.SEQUENCE_ID+ (ROW_NUMBER() OVER (PARTITION BY L1.LEGACY_ID ORDER BY null)), 
       L1.DESCR
FROM TABLE_1_PRIM_CT CTE, LEGACY_TABLE L1
WHERE L1.LEGACY_ID = CTE.PRIMARY_ID);

PS: With your Millions of Rows, this is going to create a temp table of same size, during execution. Do Explain Plan before actual execution.

Upvotes: 2

Related Questions