Inserting into subtype table values from supertype table in oracle

insertoraclesubtypes

I had created two object types and tables based on that types in oracle:

create or replace type TEMPLOYE as object(
num_emp integer,
nom_emp varchar2(40),
prenom_emp varchar2(50),
adresse_emp varchar2(200),
tel_emp varchar2(15)
) not final;
/
create or replace type TMEDECIN under TEmploye (
num_med integer,
specialite Varchar2(30)
);
/
CREATE TABLE EMPLOYEES OF TEMPLOYE;
CREATE TABLE MEDECINS OF TMEDECIN;

and I had inserted some values into EMPLOYEES like :

INSERT INTO EMPLOYEES VALUES (
TEMPLOYE (10,'BOUCHEMLA','Elias','6, hai sidi serhane -Khemis El Khechna-Boumerdes',024873549));

Finally I need to insert a record into MEDECINS table with num_med=10 and specialite='Cardiologue' and the others attributes from the parent table (EMPLOYEES), how to do that using select * statement or a reference?

Best Answer

Simply INSERT ... SELECT.

insert into
  medecins
select
  e.*,
  10,
  'Cardiologue'
from
  employees e
where
  e.num_emp = 10
;

Related Solutions

Sql-server – Create SP for inserting data from a table to a table

Before you start thinking about a solution, try to evaluate if your requirements make sense.

Is it really the best solution to create duplicate rows if multiple phone numbers exist or would it be better to create a new table PhoneNumbers with a foreign key?

And splitting data can be done more elegantly in the tool that loads the data into the database. If you have to do it in SQL, the solution depends on the database system you use. For instance, here is a solution for SQL Server: https://stackoverflow.com/q/5493510/1595345

Postgresql – Inserting into a table a row represented by JSON

I think you want something like this.

CREATE OR REPLACE FUNCTION fn_alter_entity(data JSONB, t TEXT )
  RETURNS VOID
  SECURITY INVOKER
AS $$
    BEGIN
        EXECUTE FORMAT(
          'INSERT INTO %I SELECT * FROM jsonb_populate_record( null::%I, $1);',
          t,
          t
        )
        USING data;
    END;
$$
LANGUAGE plpgsql
VOLATILE;

CREATE TABLE foo ( a int );
CREATE TABLE bar ( a int, b text );

SELECT fn_alter_entity( '{"a": 5}', 'foo' );
SELECT fn_alter_entity( '{"a": 42}', 'foo' );
SELECT fn_alter_entity( '{"a": 5, "b": "foo"}', 'bar' );
SELECT fn_alter_entity( '{"a": 42, "b": "baz"}', 'bar' );

test=# TABLE foo;
 a  
----
  5
 42
(2 rows)

test=# TABLE bar;
 a  |  b  
----+-----
  5 | foo
 42 | baz
(2 rows)

But seeing where you may be going, I would highly suggest you look into PostgREST.

Best Answer

Related Solutions

Sql-server – Create SP for inserting data from a table to a table

Postgresql – Inserting into a table a row represented by JSON

Related Question