title: Loading data from a staging table into a table with am auto-generated primary key
body: <p>I create an SQL table as shown below:</p>
  
  <pre><code>-- Create table
  create table ASSET
  (
    asset_id       NUMBER(12) default "MAS"."ASSET_SEQ"."NEXTVAL" not null,
    name           VARCHAR2(150 CHAR) not null,
    class          VARCHAR2(100 CHAR) not null,
    bucket         VARCHAR2(100 CHAR) not null,
    currency       VARCHAR2(10 CHAR) not null,
    quantity       NUMBER(12) not null,
    value          FLOAT not null,
    valuation_date DATE not null,
    expiry_date    DATE,
    fund_type      VARCHAR2(100 CHAR),
    bond_type      VARCHAR2(100 CHAR),
    isin           VARCHAR2(100 CHAR),
    underlying_id  NUMBER(12)
  )
  tablespace MAS_DATA
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      next 1M
      minextents 1
      maxextents unlimited
    );
  alter table ASSET
    add constraint PK_ASSET primary key (ASSET_ID)
    using index 
    tablespace MAS_DATA
    pctfree 10
    initrans 2
    maxtrans 255
    storage
    (
      initial 64K
      next 1M
      minextents 1
      maxextents unlimited
    );
  alter table ASSET
    add constraint FK_UNDERLYING foreign key (UNDERLYING_ID)
    references UNDERLYING (UNDERLYING_ID);
  </code></pre>
  
  <p>My <code>STAGING</code> table contains the same attributes as the <code>ASSET</code> table above, except it does not have <code>ASSET_ID</code> (the PK of the <code>ASSET</code> table), or <code>UNDERLYING_ID</code> (a FK).</p>
  
  <p>The sequence for the autogenerated <code>asset_id</code> is created with </p>
  
  <pre><code>-- Create sequence 
  create sequence ASSET_SEQ
  minvalue 1
  maxvalue 9999999999999999999999999999
  start with 844621
  increment by 1
  cache 20;
  </code></pre>
  
  <p>When I try to insert the data from my <code>STAGING</code> table using </p>
  
  <pre><code>insert into ASSET (NAME, CLASS, BUCKET, CURRENCY, QUANTITY, VALUE, VALUATION_DATE, EXPIRY_DATE, SECTOR, ISIN, UNDERLYING_ID)
         SELECT NAME, CLASS, BUCKET, CURRENCY, QUANTITY, VALUE, VALUATION_DATE, EXPIRY_DATE, SECTOR, ISIN, 0 FROM STAGING
  </code></pre>
  
  <p>The error: <code>ora-01400 cannot insert null into MAS.ASSET.NAME</code></p>
  
  <p>I think the issue is something related to <code>asset_id</code> not being defined as part of the values when inserting the values from <code>STAGING</code>, yet that value does not exist in <code>STAGING</code>, so it can't be selected from that table. Not sure where to go from there</p>