Insert into oracle database

Question

Hi I have a database with loads of columns and I want to insert couple of records for testing, now in order to insert something into that database I'd have to write large query .. is it possible to do something like this

INSERT INTO table (SELECT FROM table WHERE id='5') .. I try to insert the row with ID 5 but I think this will create a problem because it will try to duplicate a record, is it possible to change this ID 5 to let say 1000 then I'd be able to insert data without writing complex query and while doing so avoiding replication of data .. tnx

Answer 1

For each column that has no default value or you want to insert the values other than default, you will need to provide the explicit name and value.

You only can use an implicit list (*) if you want to select all columns and insert them as they are.

Since you are changing the PRIMARY KEY, you need to enumerate.

However, you can create a before update trigger and change the value of the PRIMARY KEY in this trigger.

Note that the trigger cannot reference the table itself, so you will need to provide some other way to get the unique number (like a sequence):

CREATE TRIGGER trg_mytable_bi BEFORE INSERT ON mytable FOR EACH ROW
BEGIN
    :NEW.id := s_mytable.nextval;
END;

This way you can use the asterisk but it will always replace the value of the PRIMARY KEY.

Answer 2

If you have a trigger on the table to handle the primary key from a sequence (:NEW.id = seq_sequence.NEXTVAL) then you should be able to do:

INSERT INTO table (SELECT columns_needed FROM table WHERE whatever)

This will allow you to add in many rows at one (the number being limited by the WHERE clause). You'll need to select the columns that are required by the table to be not null or not having default values. Beware of any unique constraints as well.

Otherwise you'll be looking at PL/SQL or some other form of script to insert multiple rows.

Answer 3

In PL/SQL you can do something like this:

declare
  l_rec table%rowtype;
begin
  select * into l_rec from table where id='5';
  l_rec.id := 1000;
  insert into table values l_rec;
end;