oracle sql: insert spatial data from more existing table

Question

I have created a table umriss which I filled with data and I still need to insert the geometry data from existing tables (usrdemo.glets_1850, usrdemo.glets_1973, ...). How does that work?

umriss is a "weak entity" and has references to the tables gletscherstand (glst_id) and gletscher (gletscher_id)

create table umriss
(
    umr_nr number (4) not null,
    umr_datum date,
    GLST_ID number (4) not null,
    shape mdsys.sdo_geometry,
    GLETSCHER_ID number (3) not null,
    se_anno_cad_data blob
);

alter table umriss
add constraint umriss_glst_pk
primary key (umr_nr, GLST_ID, GLETSCHER_ID);

ALTER TABLE umriss 
ADD CONSTRAINT umriss_gletscherstand_fk 
FOREIGN KEY (GLST_ID, GLETSCHER_ID) 
REFERENCES GLETSCHERSTAND(GLST_ID, GLETSCHER_ID);

I manually inserted the data for the attributes umr_nr, umr_datum, glst_id and gletscher_id. As you can see from umr_nr there are 3 shapes and I now want to add the spatial data from usrdemo_glets_1850 which has 3 shapes and the attributes: objectid (= umr_nr in table umriss), shape and se_anno_cad_data.

I tried this...

INSERT INTO umriss u
(u.shape, u.se_anno_cad_data) 
SELECT usrdemo.glets_1850.shape, usrdemo.glets_1850.se_anno_cad_data
FROM usrdemo.glets_1850;

...and got the message: Ora-01400 - cannot insert NULL into ..."umriss"."umr_nr"

How does this work?

chance chance · Accepted Answer · 2011-12-06T10:46:54

You haved tried to insert new records into table umriss without setting values for the primary key column umr_nr.

If I have understood correctly, you have already manually inserted records into table umriss, and now what you need to do is to add addtional columns only, then you should use UPDATE instead.

oracle sql: insert spatial data from more existing table

1 Answers