Clever Geek Handbook

Insert into oracle database

Hi, I have a database with many columns and I want to insert a couple of records for testing, now to insert something into this database I would have to write a big query .. is it possible to do something like this

INSERT INTO table (SELECT FROM table WHERE id='5')

.. I'm trying to insert a row with ID 5, but I think it will create a problem because it will try to duplicate the record, is it possible to change this ID 5 to say 1000, then I would be able to insert data without writing a complex query and this avoids data replication. tnx

+2


source to share


3 answers


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;
+4


source


For each column that does not have a default or you want to insert values ​​other than the default, you need to provide an explicit name and value.

You can use implicit list ( *

) if you want to select all columns and insert them as they are.

Since you are changing PRIMARY KEY

you need to list.

However, you can create a trigger before the update and change the value PRIMARY KEY

in that trigger.



Note that a trigger cannot reference a table, so you will need to provide another way to get a 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 an asterisk, but it will always replace the value PRIMARY KEY

.

0


source


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

INSERT INTO table (SELECT columns_needed FROM table WHERE)

This will allow you to add one to many rows (the number is limited by the WHERE clause). You will need to select the columns that the table requires so that they are not null or have no default values. Beware of any unique constraints.

Otherwise, you will be looking for PL / SQL or some other form of script to insert multiple rows.

0


source






More articles:


All Articles