Incrementing a value with +1 every insert

Question

I'm trying to insert 18000 values into a table, and for order_id column the value is incremented with 1, and for the rest of the columns just insert whatever the select returns. When i try to run the query it returns that variable that stores the value appears to be null .

Here's what I tried :

  DECLARE

    order_id1 number := 1;
    OrderID   VARCHAR2(100);
    i         number := 1;

  begin

    WHILE i < 18000 LOOP

      INSERT INTO Dummy_Table
        SELECT OrderID,
               CREATION_DATE,
               email,
               FIRST_NAME,
               FIX_NO,
               mobile_no,
               SECOND_NAME,
               SECOND_PHONE_NO,
               TITLE

          from Dummy_Table

      i := i + 1;
      OrderID := 'ABC' || to_char(order_id1 + 1);

    END LOOP;
  END;

In OrderID variable the string is stored which I wish to increment with 1 and insert it into order_id column from dummy_Table.

The error message is :

ORA-01400 Cannot insrt null into ("Dummy_Table"."order_id" at line 11

I swapped your MySQL tag for Oracle. Please be sure to tag the correct rdbms, as people use the tags to search for questions relating to their expertise. While some cross skilling is possible with sql, procedural code in particular is highly varied between vendors and a MySQL pro may be unable to help with an oracle question — Caius Jard, Jun 06 '19 at 04:28

score 1 · Answer 1 · answered Jun 06 '19 at 04:33

1

You declare orderID but don't set a value

Then you try to insert this null value (error happens here)

Then you set a value for orderID

Swap the order of your operations around (assignment is above insert) so that it is given a value before the first attempt at inserting it or give orderID an initial value in the declaration section like you do with the other two variables

Incidentally, I believe this code will only insert 17999 values, which is one short of your "I need 18000 values in my table"

answered Jun 06 '19 at 04:33

Caius Jard

72,509
5
49
80

I've move it before the insert and it returns the following error message `Unique constraint (SomeCode) violated, at line 13` – Gabriel Alin Jun 06 '19 at 04:39
Can you please add the structure of Dummy_Table to your question (including constrains PK, etc)? – alexs Jun 06 '19 at 07:29
The "unique constraint violated" is nothing to do with the position of the code; it's that you're trying to insert a value that already exists. This error is hidden by your current error (cannot insert null) but moving the code line solves the "insert null" problem and reveals the "inserting duplicate" problem. It does not "cause" the inserting duplicates problem. Delete all the data in the table or choose a starting value / pattern that ensure s inserted data is unique – Caius Jard Jun 06 '19 at 07:41

score 1 · Answer 2 · answered Jun 06 '19 at 04:47

1

It seems since you assign a value only in the end of the loop. OrderId is always null in the first iteration. You have to assign a value before running the insert statement.

While 
  OrderId := 'ABC' || to_char(order_id);
  Insert ...;
  order_id ++;

answered Jun 06 '19 at 04:47

Fabian Börner

168
1
2
9

score 0 · Accepted Answer · answered Jun 06 '19 at 04:42

OrderID is null in the first iteration of the loop.

You might want to initialize OrderID:

OrderID   VARCHAR2(100) := 'ABC1'

, or assign a value before insert:

OrderID := 'ABC' || to_char(order_id1 + 1);
order_id1:= order_id1 +1; -- you didn't increment order_id1 for some reason
INSERT INTO Dummy_Table

, or you can get rid of both OrderID adn order_id1 variables:

INSERT INTO Dummy_Table
    SELECT 'ABC'||i,
           CREATION_DATE,
           email,
           FIRST_NAME,
           FIX_NO,
           mobile_no,
           SECOND_NAME,
           SECOND_PHONE_NO,
           TITLE
     from Dummy_Table;
i:=i+1;

, or you can generate a list of numbers and insert all the records at once without using a loop:

INSERT INTO Dummy_Table
    SELECT 'ABC'||ids.id,
           CREATION_DATE,
           email,
           FIRST_NAME,
           FIX_NO,
           mobile_no,
           SECOND_NAME,
           SECOND_PHONE_NO,
           TITLE
     from Dummy_Table d,
          (SELECT LEVEL id
           FROM DUAL
           CONNECT BY LEVEL < 18000) ids;

Incrementing a value with +1 every insert

3 Answers3