Results 1 to 12 of 12
  1. #1
    Join Date
    Dec 2004
    Posts
    19

    Unanswered: Multi Table Insert problem

    Hi,
    I am trying to use Oracle Multi table Insert but keep getting errors. My sql is as below.
    If you need table structure, pl. let me know. Is it bug?

    Table INFO A==> Email is PK
    Table Receive_INFO B==>receive_info_id is PK and Email is FK Ref to Table INFO A
    Table Attribute C ==> Attribute_Id is a PK and receive_info_id is FK ref to Table Receive_INFO B


    INSERT ALL
    INTO USER
    (EMAIL,
    FIRST_NAME ,
    LAST_NAME ,
    DATE_CREATED
    )
    VALUES
    (EMAIL1 ,
    FIRST_NAME ,
    LAST_NAME ,
    DATE_CREATED
    )
    INTO ADDRESS
    (ADDRESS_ID ,
    FIRST_NAME ,
    LAST_NAME ,
    ADDRESS1 ,
    ADDRESS2 ,
    CITY ,
    STATE ,
    POSTAL ,
    COUNTRY ,
    DAY_PHONE ,
    EVENING_PHONE
    )
    VALUES
    (ADDRESS_SEQ.nextval,
    FIRST_NAME ,
    LAST_NAME ,
    ADDRESS1 ,
    ADDRESS2 ,
    CITY ,
    STATE_PROVINCE ,
    POSTAL_CODE ,
    COUNTRY ,
    DAY_PHONE ,
    NIGHT_PHONE
    )
    INTO GROUP
    (GROUP_ID,
    DIVISION_ID,
    WEBSITE ,
    SECTION
    )
    VALUES
    (GROUP_SEQ.nextval,
    DIVISION_ID,
    WEBSITE ,
    SECTION
    )
    INTO USER_ADDRESS
    (
    EMAIL ,
    ADDRESS_ID ,
    ADDRESS_TYPE ,
    GROUP_id
    )
    VALUES
    (EMAIL1,
    ADDRESS_SEQ.currval ,
    'HOME' ,
    GROUP_SEQ.currval
    )
    INTO USER_GROUP
    (
    EMAIL,
    GROUP_ID ,
    USER_DATE ,
    RECEIVE_EMAIL
    )
    VALUES
    (EMAIL1,
    GROUP_SEQ.currval ,
    sysdate,
    RECEIVE_EMAIL
    )
    INTO USER_ATTRIBUTE
    (ID,
    EMAIL,
    TYPE ,
    VALUE ,
    USER_DATE
    )
    VALUES
    (USER_ATTRIBUTE_SEQ.nextval,
    EMAIL2,
    ATTRIBUTE_TYPE,
    ATTRIBUTE_VALUE,
    sysdate
    )
    SELECT A.EMAIL email1,FIRST_NAME, LAST_NAME,
    ADDRESS1, ADDRESS2, CITY, STATE_PROVINCE, POSTAL_CODE, COUNTRY, DAY_PHONE, NIGHT_PHONE,
    A.DATE_CREATED,
    B.RECEIVE_INFO_ID, B.EMAIL email2, DIVISION_ID, WEBSITE, SECTION,
    RECEIVE_EMAIL, B.DATE_CREATED,
    C.ATTRIBUTE_ID,
    C.RECEIVE_INFO_ID,
    C.ATTRIBUTE_TYPE ,
    C.ATTRIBUTE_VALUE
    FROM INFO A, RECEIVE_INFO B, ATTRIBUTE C
    WHERE A.EMAIL = B.EMAIL
    AND B.RECEIVE_INFO_ID = C.RECEIVE_INFO_ID
    and DIVISION_ID = 'DIV1'
    and rownum <= 10

    GROUP_SEQ.currval
    *
    ERROR at line 71:
    ORA-02287: sequence number not allowed here

    thanks,

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    For a multitable insert operation, each expression in the values_clause must refer to columns returned by the select list of the subquery.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Dec 2004
    Posts
    19

    Multitable Insert

    For a multitable insert operation, each expression in the values_clause must refer to returned by the select list of the subquery.:

    Thanks for your resposne.
    That's I am doing here, only i am inserting sequence from out side.

    Thanks,

  4. #4
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    LKBrwn_DBA, not likely.
    Code:
    SQL> create table t1 as
      2  select 0 id, object_name
      3    from all_objects
      4   where 1 = 0;
    
    Table created.
    
    SQL> create table t2 as
      2  select 0 id, object_name
      3    from all_objects
      4   where 1 = 0;
    
    Table created.
    
    SQL> create sequence a;
    
    Sequence created.
    
    SQL> rem to avoidi ora-08002...
    SQL> select a.nextval from dual;
    
       NEXTVAL
    ----------
             1
    
    SQL> insert all
      2    into t1 ( id, object_name )
      3     values ( a.currval, object_name )
      4    into t2 ( object_name, id )
      5     values ( object_name, a.currval )
      6  select to_char( object_name ) object_name
      7    from all_objects
      8   where rownum <= 10;
    
    20 rows created.
    What they say here is that you cannot move the sequence into the subquery itself, that will raise a ORA-02287.

    poratips, I cannot reproduce. Need table definitions, etc..

  5. #5
    Join Date
    Dec 2004
    Posts
    19

    Multitable Insert

    Script for table and data.
    CREATE TABLE INFO
    (
    EMAIL VARCHAR2(100 BYTE) NOT NULL,
    TITLE VARCHAR2(40 BYTE),
    FIRST_NAME VARCHAR2(40 BYTE),
    MIDDLE_NAME VARCHAR2(40 BYTE),
    LAST_NAME VARCHAR2(40 BYTE),
    ADDRESS1 VARCHAR2(40 BYTE),
    ADDRESS2 VARCHAR2(40 BYTE),
    CITY VARCHAR2(30 BYTE),
    STATE_PROVINCE VARCHAR2(30 BYTE),
    POSTAL_CODE VARCHAR2(10 BYTE),
    COUNTRY VARCHAR2(40 BYTE),
    DAY_PHONE VARCHAR2(15 BYTE),
    NIGHT_PHONE VARCHAR2(15 BYTE),
    DATE_CREATED DATE NOT NULL,
    GENDER VARCHAR2(1 BYTE),
    BIRTH_DATE DATE,
    COMPANY_NAME VARCHAR2(100 BYTE),
    ACTIVE_FLAG VARCHAR2(1 BYTE) DEFAULT 'Y',
    DATE_UPDATED DATE,
    PASSWORD VARCHAR2(35 BYTE)
    )

    ALTER TABLE INFO ADD (
    CONSTRAINT INFO_PK PRIMARY KEY (EMAIL));
    /
    INSERT INTO INFO ( EMAIL, TITLE, FIRST_NAME, MIDDLE_NAME, LAST_NAME, ADDRESS1, ADDRESS2,
    CITY, STATE_PROVINCE, POSTAL_CODE, COUNTRY, DAY_PHONE, NIGHT_PHONE, DATE_CREATED, GENDER,
    BIRTH_DATE, COMPANY_NAME, ACTIVE_FLAG, DATE_UPDATED, PASSWORD ) VALUES (
    'imltda@yahoo.com', NULL, 'Gonzalo Moreno', NULL, NULL, NULL, NULL, NULL, NULL, NULL
    , 'Colombia', '6331125, 6331126', NULL, TO_Date( '07/22/2005 10:19:36 PM', 'MM/DD/YYYY HH:MIS AM')
    , NULL, NULL, NULL, 'Y', NULL, NULL);
    INSERT INTO INFO ( EMAIL, TITLE, FIRST_NAME, MIDDLE_NAME, LAST_NAME, ADDRESS1, ADDRESS2,
    CITY, STATE_PROVINCE, POSTAL_CODE, COUNTRY, DAY_PHONE, NIGHT_PHONE, DATE_CREATED, GENDER,
    BIRTH_DATE, COMPANY_NAME, ACTIVE_FLAG, DATE_UPDATED, PASSWORD ) VALUES (
    'peterbuz@hotmail.com', NULL, 'PEDRO MEZA', NULL, NULL, NULL, NULL, NULL, NULL, NULL
    , 'Mexico', '38120958', NULL, TO_Date( '07/22/2005 11:00:35 AM', 'MM/DD/YYYY HH:MIS AM')
    , NULL, NULL, NULL, 'Y', NULL, NULL);
    INSERT INTO INFO ( EMAIL, TITLE, FIRST_NAME, MIDDLE_NAME, LAST_NAME, ADDRESS1, ADDRESS2,
    CITY, STATE_PROVINCE, POSTAL_CODE, COUNTRY, DAY_PHONE, NIGHT_PHONE, DATE_CREATED, GENDER,
    BIRTH_DATE, COMPANY_NAME, ACTIVE_FLAG, DATE_UPDATED, PASSWORD ) VALUES (
    'frutilupisgg@hotmail.com', NULL, 'Guadalupe granados garcía', NULL, NULL, NULL, NULL
    , NULL, NULL, NULL, 'Mexico', '51155062', NULL, TO_Date( '07/21/2005 11:51:08 PM', 'MM/DD/YYYY HH:MIS AM')
    , NULL, NULL, NULL, 'Y', NULL, NULL);
    INSERT INTO INFO ( EMAIL, TITLE, FIRST_NAME, MIDDLE_NAME, LAST_NAME, ADDRESS1, ADDRESS2,
    CITY, STATE_PROVINCE, POSTAL_CODE, COUNTRY, DAY_PHONE, NIGHT_PHONE, DATE_CREATED, GENDER,
    BIRTH_DATE, COMPANY_NAME, ACTIVE_FLAG, DATE_UPDATED, PASSWORD ) VALUES (
    'camilaarcoiris@hotmail.com', NULL, 'camila chiara ciocoletto', NULL, NULL, NULL
    , NULL, NULL, NULL, NULL, 'Latin America', '47572053', NULL, TO_Date( '03/12/2005 04:55:14 PM', 'MM/DD/YYYY HH:MIS AM')
    , NULL, NULL, NULL, 'Y', NULL, NULL);
    INSERT INTO INFO ( EMAIL, TITLE, FIRST_NAME, MIDDLE_NAME, LAST_NAME, ADDRESS1, ADDRESS2,
    CITY, STATE_PROVINCE, POSTAL_CODE, COUNTRY, DAY_PHONE, NIGHT_PHONE, DATE_CREATED, GENDER,
    BIRTH_DATE, COMPANY_NAME, ACTIVE_FLAG, DATE_UPDATED, PASSWORD ) VALUES (
    'ikaki99@gmail.com', NULL, 'Iñaki Crujeiras', NULL, NULL, NULL, NULL, NULL, NULL, NULL
    , 'Mexico', '012222853869', NULL, TO_Date( '03/14/2005 11:53:39 AM', 'MM/DD/YYYY HH:MIS AM')
    , NULL, NULL, NULL, 'Y', NULL, NULL);

    CREATE TABLE RECEIVE_INFO
    (
    RECEIVE_INFO_ID VARCHAR2(40 BYTE) NOT NULL,
    EMAIL VARCHAR2(100 BYTE) NOT NULL,
    DIVISION_ID VARCHAR2(20 BYTE) NOT NULL,
    WEBSITE VARCHAR2(100 BYTE) NOT NULL,
    SECTION VARCHAR2(100 BYTE),
    RECEIVE_EMAIL VARCHAR2(1 BYTE),
    DATE_CREATED DATE NOT NULL,
    RECEIVE_EMAIL_FROM_STORE VARCHAR2(50 BYTE),
    DATE_UPDATED DATE,
    UPDATED_BY VARCHAR2(40 BYTE)
    )
    ALTER TABLE RECEIVE_INFO ADD (
    CONSTRAINT RECEIVE_INFO_PK PRIMARY KEY (RECEIVE_INFO_ID));


    ALTER TABLE RECEIVE_INFO ADD (
    CONSTRAINT RECEIVE_INFO_FK FOREIGN KEY (EMAIL)
    REFERENCES INFO (EMAIL) );
    /

    INSERT INTO RECEIVE_INFO ( RECEIVE_INFO_ID, EMAIL, DIVISION_ID, WEBSITE,
    SECTION, RECEIVE_EMAIL, RECEIVE_CATALOG, DATE_CREATED, RECEIVE_EMAIL_FROM_STORE, DATE_UPDATED,
    UPDATED_BY ) VALUES (
    'SNB2BAgo100161', 'msaizd@petrobras.com.ar', 'SNLA', 'Sanfordcorp LA Web Site', 'Argentina Upload Sept 2005'
    , 'Y', NULL, TO_Date( '09/21/2005 12:15:30 PM', 'MM/DD/YYYY HH:MIS AM'), NULL, NULL
    , NULL);
    INSERT INTO RECEIVE_INFO ( RECEIVE_INFO_ID, EMAIL, DIVISION_ID, WEBSITE,
    SECTION, RECEIVE_EMAIL, RECEIVE_CATALOG, DATE_CREATED, RECEIVE_EMAIL_FROM_STORE, DATE_UPDATED,
    UPDATED_BY ) VALUES (
    '354703940', 'mar_zarco@hotmail.com', 'SNLA', 'Sanfordcorp LA Web Site', 'Contact Us'
    , 'Y', 'N', TO_Date( '07/20/2005 12:17:11 PM', 'MM/DD/YYYY HH:MIS AM'), NULL, NULL
    , NULL);
    INSERT INTO RECEIVE_INFO ( RECEIVE_INFO_ID, EMAIL, DIVISION_ID, WEBSITE,
    SECTION, RECEIVE_EMAIL, RECEIVE_CATALOG, DATE_CREATED, RECEIVE_EMAIL_FROM_STORE, DATE_UPDATED,
    UPDATED_BY ) VALUES (
    '349801322', 'mar_zarco@hotmail.com', 'SNLA', 'Sanfordcorp LA Web Site', 'Contact Us'
    , 'Y', 'N', TO_Date( '07/14/2005 09:46:50 AM', 'MM/DD/YYYY HH:MIS AM'), NULL, NULL
    , NULL);
    CREATE TABLE ATTRIBUTE
    (
    ATTRIBUTE_ID VARCHAR2(40 BYTE) NOT NULL,
    RECEIVE_INFO_ID VARCHAR2(40 BYTE) NOT NULL,
    ATTRIBUTE_TYPE VARCHAR2(100 BYTE) NOT NULL,
    ATTRIBUTE_VALUE VARCHAR2(4000 BYTE) NOT NULL
    )

    ALTER TABLE ATTRIBUTE ADD (
    CONSTRAINT ATTRIBUTE_PK PRIMARY KEY (ATTRIBUTE_ID));


    ALTER TABLE ATTRIBUTE ADD (
    CONSTRAINT ATTRIBUTE_FK FOREIGN KEY (RECEIVE_INFO_ID)
    REFERENCES RECEIVE_INFO (RECEIVE_INFO_ID));

    INSERT INTO ATTRIBUTE ( ATTRIBUTE_ID, RECEIVE_INFO_ID, ATTRIBUTE_TYPE,
    ATTRIBUTE_VALUE ) VALUES (
    '240400208', '245700056', 'additionalinfo', 'test');
    INSERT INTO ATTRIBUTE ( ATTRIBUTE_ID, RECEIVE_INFO_ID, ATTRIBUTE_TYPE,
    ATTRIBUTE_VALUE ) VALUES (
    '240400210', '245700056', 'comments', 'test');
    INSERT INTO ATTRIBUTE ( ATTRIBUTE_ID, RECEIVE_INFO_ID, ATTRIBUTE_TYPE,
    ATTRIBUTE_VALUE ) VALUES (
    '240400212', '245700056', 'productsinterested', 'test');
    INSERT INTO ATTRIBUTE ( ATTRIBUTE_ID, RECEIVE_INFO_ID, ATTRIBUTE_TYPE,
    ATTRIBUTE_VALUE ) VALUES (
    '239700838', '245000228', 'additionalinfo', 'Buscador');
    INSERT INTO ATTRIBUTE ( ATTRIBUTE_ID, RECEIVE_INFO_ID, ATTRIBUTE_TYPE,
    ATTRIBUTE_VALUE ) VALUES (
    '239700842', '245000228', 'productsinterested', 'Esterbrook');
    INSERT INTO ATTRIBUTE ( ATTRIBUTE_ID, RECEIVE_INFO_ID, ATTRIBUTE_TYPE,
    ATTRIBUTE_VALUE ) VALUES (
    '240201850', '245500490', 'productsinterested', 'Berol - Esterbrook');


    CREATE TABLE USERt
    (
    EMAIL VARCHAR2(100 BYTE) NOT NULL,
    TITLE VARCHAR2(40 BYTE),
    FIRST_NAME VARCHAR2(40 BYTE),
    MIDDLE_NAME VARCHAR2(40 BYTE),
    LAST_NAME VARCHAR2(40 BYTE),
    DATE_CREATED DATE NOT NULL
    )
    ALTER TABLE USERt ADD (
    CONSTRAINT tUSER_PK PRIMARY KEY (EMAIL));
    /

    CREATE TABLE ADDRESS
    (
    ADDRESS_ID VARCHAR2(20 BYTE) NOT NULL,
    FIRST_NAME VARCHAR2(40 BYTE),
    LAST_NAME VARCHAR2(40 BYTE),
    ADDRESS1 VARCHAR2(40 BYTE),
    ADDRESS2 VARCHAR2(40 BYTE),
    CITY VARCHAR2(40 BYTE),
    STATE VARCHAR2(40 BYTE),
    POSTAL VARCHAR2(10 BYTE),
    COUNTRY VARCHAR2(40 BYTE),
    DAY_PHONE VARCHAR2(20 BYTE),
    EVENING_PHONE VARCHAR2(20 BYTE)
    )
    ALTER TABLE ADDRESS ADD (
    CONSTRAINT ADDRESS_PK PRIMARY KEY (ADDRESS_ID));
    /
    CREATE TABLE GROUPt
    (
    GROUP_ID VARCHAR2(20 BYTE) NOT NULL,
    DIVISION_ID VARCHAR2(10 BYTE) NOT NULL,
    WEBSITE VARCHAR2(100 BYTE) NOT NULL,
    SECTION VARCHAR2(100 BYTE)
    )
    ALTER TABLE GROUP ADD (
    CONSTRAINT GROUP_PK PRIMARY KEY (GROUP_ID));

    CREATE TABLE USER_ADDRESS
    (
    EMAIL VARCHAR2(100 BYTE) NOT NULL,
    ADDRESS_ID VARCHAR2(20 BYTE) NOT NULL,
    ADDRESS_TYPE VARCHAR2(20 BYTE) NOT NULL,
    GROUP_ID VARCHAR2(20 BYTE) NOT NULL
    )

    ALTER TABLE USER_ADDRESS ADD (
    CONSTRAINT USER_ADDRESS_FK FOREIGN KEY (EMAIL)
    REFERENCES USERt (EMAIL));

    ALTER TABLE USER_ADDRESS ADD (
    CONSTRAINT USER_ADDRESS_FK1 FOREIGN KEY (ADDRESS_ID)
    REFERENCES ADDRESS (ADDRESS_ID));

    ALTER TABLE NUSER_ADDRESS ADD (
    CONSTRAINT USER_ADDRESS_FK2 FOREIGN KEY (GROUP_ID)
    REFERENCES GROUPt (GROUP_ID));

    CREATE TABLE USER_GROUP
    (
    EMAIL VARCHAR2(100 BYTE) NOT NULL,
    GROUP_ID VARCHAR2(20 BYTE) NOT NULL,
    USER_DATE DATE NOT NULL,
    RECEIVE_EMAIL VARCHAR2(1 BYTE)
    )

    ALTER TABLE USER_GROUP ADD (
    CONSTRAINT USER_GROUP_PK PRIMARY KEY (EMAIL, GROUP_ID, USER_DATE));


    ALTER TABLE USER_GROUP ADD (
    CONSTRAINT USER_GROUP_FK FOREIGN KEY (EMAIL)
    REFERENCES USERt (EMAIL));

    ALTER TABLE USER_GROUP ADD (
    CONSTRAINT USER_GROUP_FK1 FOREIGN KEY (GROUP_ID)
    REFERENCES GROUPt (GROUP_ID));

    CREATE TABLE USER_ATTRIBUTE
    (
    ID VARCHAR2(20 BYTE) NOT NULL,
    EMAIL VARCHAR2(100 BYTE) NOT NULL,
    TYPE VARCHAR2(100 BYTE) NOT NULL,
    VALUE VARCHAR2(4000 BYTE) NOT NULL,
    USER_DATE DATE NOT NULL
    )

    ALTER TABLE USER_ATTRIBUTE ADD (
    CONSTRAINT USER_ATTRIBUTE_PK PRIMARY KEY (ID));


    ALTER TABLE USER_ATTRIBUTE ADD (
    CONSTRAINT USER_ATTRIBUTE_FK FOREIGN KEY (EMAIL)
    REFERENCES USERt (EMAIL));

  6. #6
    Join Date
    Dec 2004
    Posts
    19

    Multi Insert

    Is it this help to resolve the problem as now I ma getting Unique constrainr error.

    thanks,

    CREATE TABLE INFO
    (
    EMAIL VARCHAR2(100 BYTE) NOT NULL,
    TITLE VARCHAR2(40 BYTE),
    FIRST_NAME VARCHAR2(40 BYTE),
    MIDDLE_NAME VARCHAR2(40 BYTE),
    LAST_NAME VARCHAR2(40 BYTE),
    ADDRESS1 VARCHAR2(40 BYTE),
    ADDRESS2 VARCHAR2(40 BYTE),
    CITY VARCHAR2(30 BYTE),
    STATE_PROVINCE VARCHAR2(30 BYTE),
    POSTAL_CODE VARCHAR2(10 BYTE),
    COUNTRY VARCHAR2(40 BYTE),
    DAY_PHONE VARCHAR2(15 BYTE),
    NIGHT_PHONE VARCHAR2(15 BYTE),
    DATE_CREATED DATE NOT NULL,
    GENDER VARCHAR2(1 BYTE),
    BIRTH_DATE DATE,
    COMPANY_NAME VARCHAR2(100 BYTE),
    ACTIVE_FLAG VARCHAR2(1 BYTE) DEFAULT 'Y',
    DATE_UPDATED DATE,
    PASSWORD VARCHAR2(35 BYTE)
    )

    ALTER TABLE INFO ADD (
    CONSTRAINT INFO_PK PRIMARY KEY (EMAIL));
    /
    INSERT INTO INFO ( EMAIL, TITLE, FIRST_NAME, MIDDLE_NAME, LAST_NAME, ADDRESS1, ADDRESS2,
    CITY, STATE_PROVINCE, POSTAL_CODE, COUNTRY, DAY_PHONE, NIGHT_PHONE, DATE_CREATED, GENDER,
    BIRTH_DATE, COMPANY_NAME, ACTIVE_FLAG, DATE_UPDATED, PASSWORD ) VALUES (
    'imltda@yahoo.com', NULL, 'Gonzalo Moreno', NULL, NULL, NULL, NULL, NULL, NULL, NULL
    , 'Colombia', '6331125, 6331126', NULL, TO_Date( '07/22/2005 10:19:36 PM', 'MM/DD/YYYY HH:MIS AM')
    , NULL, NULL, NULL, 'Y', NULL, NULL);
    INSERT INTO INFO ( EMAIL, TITLE, FIRST_NAME, MIDDLE_NAME, LAST_NAME, ADDRESS1, ADDRESS2,
    CITY, STATE_PROVINCE, POSTAL_CODE, COUNTRY, DAY_PHONE, NIGHT_PHONE, DATE_CREATED, GENDER,
    BIRTH_DATE, COMPANY_NAME, ACTIVE_FLAG, DATE_UPDATED, PASSWORD ) VALUES (
    'peterbuz@hotmail.com', NULL, 'PEDRO MEZA', NULL, NULL, NULL, NULL, NULL, NULL, NULL
    , 'Mexico', '38120958', NULL, TO_Date( '07/22/2005 11:00:35 AM', 'MM/DD/YYYY HH:MIS AM')
    , NULL, NULL, NULL, 'Y', NULL, NULL);
    INSERT INTO INFO ( EMAIL, TITLE, FIRST_NAME, MIDDLE_NAME, LAST_NAME, ADDRESS1, ADDRESS2,
    CITY, STATE_PROVINCE, POSTAL_CODE, COUNTRY, DAY_PHONE, NIGHT_PHONE, DATE_CREATED, GENDER,
    BIRTH_DATE, COMPANY_NAME, ACTIVE_FLAG, DATE_UPDATED, PASSWORD ) VALUES (
    'frutilupisgg@hotmail.com', NULL, 'Guadalupe granados garcía', NULL, NULL, NULL, NULL
    , NULL, NULL, NULL, 'Mexico', '51155062', NULL, TO_Date( '07/21/2005 11:51:08 PM', 'MM/DD/YYYY HH:MIS AM')
    , NULL, NULL, NULL, 'Y', NULL, NULL);
    INSERT INTO INFO ( EMAIL, TITLE, FIRST_NAME, MIDDLE_NAME, LAST_NAME, ADDRESS1, ADDRESS2,
    CITY, STATE_PROVINCE, POSTAL_CODE, COUNTRY, DAY_PHONE, NIGHT_PHONE, DATE_CREATED, GENDER,
    BIRTH_DATE, COMPANY_NAME, ACTIVE_FLAG, DATE_UPDATED, PASSWORD ) VALUES (
    'camilaarcoiris@hotmail.com', NULL, 'camila chiara ciocoletto', NULL, NULL, NULL
    , NULL, NULL, NULL, NULL, 'Latin America', '47572053', NULL, TO_Date( '03/12/2005 04:55:14 PM', 'MM/DD/YYYY HH:MIS AM')
    , NULL, NULL, NULL, 'Y', NULL, NULL);
    INSERT INTO INFO ( EMAIL, TITLE, FIRST_NAME, MIDDLE_NAME, LAST_NAME, ADDRESS1, ADDRESS2,
    CITY, STATE_PROVINCE, POSTAL_CODE, COUNTRY, DAY_PHONE, NIGHT_PHONE, DATE_CREATED, GENDER,
    BIRTH_DATE, COMPANY_NAME, ACTIVE_FLAG, DATE_UPDATED, PASSWORD ) VALUES (
    'ikaki99@gmail.com', NULL, 'Iñaki Crujeiras', NULL, NULL, NULL, NULL, NULL, NULL, NULL
    , 'Mexico', '012222853869', NULL, TO_Date( '03/14/2005 11:53:39 AM', 'MM/DD/YYYY HH:MIS AM')
    , NULL, NULL, NULL, 'Y', NULL, NULL);

    CREATE TABLE RECEIVE_INFO
    (
    RECEIVE_INFO_ID VARCHAR2(40 BYTE) NOT NULL,
    EMAIL VARCHAR2(100 BYTE) NOT NULL,
    DIVISION_ID VARCHAR2(20 BYTE) NOT NULL,
    WEBSITE VARCHAR2(100 BYTE) NOT NULL,
    SECTION VARCHAR2(100 BYTE),
    RECEIVE_EMAIL VARCHAR2(1 BYTE),
    DATE_CREATED DATE NOT NULL,
    RECEIVE_EMAIL_FROM_STORE VARCHAR2(50 BYTE),
    DATE_UPDATED DATE,
    UPDATED_BY VARCHAR2(40 BYTE)
    )
    ALTER TABLE RECEIVE_INFO ADD (
    CONSTRAINT RECEIVE_INFO_PK PRIMARY KEY (RECEIVE_INFO_ID));


    ALTER TABLE RECEIVE_INFO ADD (
    CONSTRAINT RECEIVE_INFO_FK FOREIGN KEY (EMAIL)
    REFERENCES INFO (EMAIL) );
    /

    INSERT INTO RECEIVE_INFO ( RECEIVE_INFO_ID, EMAIL, DIVISION_ID, WEBSITE,
    SECTION, RECEIVE_EMAIL, RECEIVE_CATALOG, DATE_CREATED, RECEIVE_EMAIL_FROM_STORE, DATE_UPDATED,
    UPDATED_BY ) VALUES (
    'SNB2BAgo100161', 'msaizd@petrobras.com.ar', 'SNLA', 'Sanfordcorp LA Web Site', 'Argentina Upload Sept 2005'
    , 'Y', NULL, TO_Date( '09/21/2005 12:15:30 PM', 'MM/DD/YYYY HH:MIS AM'), NULL, NULL
    , NULL);
    INSERT INTO RECEIVE_INFO ( RECEIVE_INFO_ID, EMAIL, DIVISION_ID, WEBSITE,
    SECTION, RECEIVE_EMAIL, RECEIVE_CATALOG, DATE_CREATED, RECEIVE_EMAIL_FROM_STORE, DATE_UPDATED,
    UPDATED_BY ) VALUES (
    '354703940', 'mar_zarco@hotmail.com', 'SNLA', 'Sanfordcorp LA Web Site', 'Contact Us'
    , 'Y', 'N', TO_Date( '07/20/2005 12:17:11 PM', 'MM/DD/YYYY HH:MIS AM'), NULL, NULL
    , NULL);
    INSERT INTO RECEIVE_INFO ( RECEIVE_INFO_ID, EMAIL, DIVISION_ID, WEBSITE,
    SECTION, RECEIVE_EMAIL, RECEIVE_CATALOG, DATE_CREATED, RECEIVE_EMAIL_FROM_STORE, DATE_UPDATED,
    UPDATED_BY ) VALUES (
    '349801322', 'mar_zarco@hotmail.com', 'SNLA', 'Sanfordcorp LA Web Site', 'Contact Us'
    , 'Y', 'N', TO_Date( '07/14/2005 09:46:50 AM', 'MM/DD/YYYY HH:MIS AM'), NULL, NULL
    , NULL);
    CREATE TABLE ATTRIBUTE
    (
    ATTRIBUTE_ID VARCHAR2(40 BYTE) NOT NULL,
    RECEIVE_INFO_ID VARCHAR2(40 BYTE) NOT NULL,
    ATTRIBUTE_TYPE VARCHAR2(100 BYTE) NOT NULL,
    ATTRIBUTE_VALUE VARCHAR2(4000 BYTE) NOT NULL
    )

    ALTER TABLE ATTRIBUTE ADD (
    CONSTRAINT ATTRIBUTE_PK PRIMARY KEY (ATTRIBUTE_ID));


    ALTER TABLE ATTRIBUTE ADD (
    CONSTRAINT ATTRIBUTE_FK FOREIGN KEY (RECEIVE_INFO_ID)
    REFERENCES RECEIVE_INFO (RECEIVE_INFO_ID));

    INSERT INTO ATTRIBUTE ( ATTRIBUTE_ID, RECEIVE_INFO_ID, ATTRIBUTE_TYPE,
    ATTRIBUTE_VALUE ) VALUES (
    '240400208', '245700056', 'additionalinfo', 'test');
    INSERT INTO ATTRIBUTE ( ATTRIBUTE_ID, RECEIVE_INFO_ID, ATTRIBUTE_TYPE,
    ATTRIBUTE_VALUE ) VALUES (
    '240400210', '245700056', 'comments', 'test');
    INSERT INTO ATTRIBUTE ( ATTRIBUTE_ID, RECEIVE_INFO_ID, ATTRIBUTE_TYPE,
    ATTRIBUTE_VALUE ) VALUES (
    '240400212', '245700056', 'productsinterested', 'test');
    INSERT INTO ATTRIBUTE ( ATTRIBUTE_ID, RECEIVE_INFO_ID, ATTRIBUTE_TYPE,
    ATTRIBUTE_VALUE ) VALUES (
    '239700838', '245000228', 'additionalinfo', 'Buscador');
    INSERT INTO ATTRIBUTE ( ATTRIBUTE_ID, RECEIVE_INFO_ID, ATTRIBUTE_TYPE,
    ATTRIBUTE_VALUE ) VALUES (
    '239700842', '245000228', 'productsinterested', 'Esterbrook');
    INSERT INTO ATTRIBUTE ( ATTRIBUTE_ID, RECEIVE_INFO_ID, ATTRIBUTE_TYPE,
    ATTRIBUTE_VALUE ) VALUES (
    '240201850', '245500490', 'productsinterested', 'Berol - Esterbrook');


    CREATE TABLE USERt
    (
    EMAIL VARCHAR2(100 BYTE) NOT NULL,
    TITLE VARCHAR2(40 BYTE),
    FIRST_NAME VARCHAR2(40 BYTE),
    MIDDLE_NAME VARCHAR2(40 BYTE),
    LAST_NAME VARCHAR2(40 BYTE),
    DATE_CREATED DATE NOT NULL
    )
    ALTER TABLE USERt ADD (
    CONSTRAINT tUSER_PK PRIMARY KEY (EMAIL));
    /

    CREATE TABLE ADDRESS
    (
    ADDRESS_ID VARCHAR2(20 BYTE) NOT NULL,
    FIRST_NAME VARCHAR2(40 BYTE),
    LAST_NAME VARCHAR2(40 BYTE),
    ADDRESS1 VARCHAR2(40 BYTE),
    ADDRESS2 VARCHAR2(40 BYTE),
    CITY VARCHAR2(40 BYTE),
    STATE VARCHAR2(40 BYTE),
    POSTAL VARCHAR2(10 BYTE),
    COUNTRY VARCHAR2(40 BYTE),
    DAY_PHONE VARCHAR2(20 BYTE),
    EVENING_PHONE VARCHAR2(20 BYTE)
    )
    ALTER TABLE ADDRESS ADD (
    CONSTRAINT ADDRESS_PK PRIMARY KEY (ADDRESS_ID));
    /
    CREATE TABLE GROUPt
    (
    GROUP_ID VARCHAR2(20 BYTE) NOT NULL,
    DIVISION_ID VARCHAR2(10 BYTE) NOT NULL,
    WEBSITE VARCHAR2(100 BYTE) NOT NULL,
    SECTION VARCHAR2(100 BYTE)
    )
    ALTER TABLE GROUP ADD (
    CONSTRAINT GROUP_PK PRIMARY KEY (GROUP_ID));

    CREATE TABLE USER_ADDRESS
    (
    EMAIL VARCHAR2(100 BYTE) NOT NULL,
    ADDRESS_ID VARCHAR2(20 BYTE) NOT NULL,
    ADDRESS_TYPE VARCHAR2(20 BYTE) NOT NULL,
    GROUP_ID VARCHAR2(20 BYTE) NOT NULL
    )

    ALTER TABLE USER_ADDRESS ADD (
    CONSTRAINT USER_ADDRESS_FK FOREIGN KEY (EMAIL)
    REFERENCES USERt (EMAIL));

    ALTER TABLE USER_ADDRESS ADD (
    CONSTRAINT USER_ADDRESS_FK1 FOREIGN KEY (ADDRESS_ID)
    REFERENCES ADDRESS (ADDRESS_ID));

    ALTER TABLE NUSER_ADDRESS ADD (
    CONSTRAINT USER_ADDRESS_FK2 FOREIGN KEY (GROUP_ID)
    REFERENCES GROUPt (GROUP_ID));

    CREATE TABLE USER_GROUP
    (
    EMAIL VARCHAR2(100 BYTE) NOT NULL,
    GROUP_ID VARCHAR2(20 BYTE) NOT NULL,
    USER_DATE DATE NOT NULL,
    RECEIVE_EMAIL VARCHAR2(1 BYTE)
    )

    ALTER TABLE USER_GROUP ADD (
    CONSTRAINT USER_GROUP_PK PRIMARY KEY (EMAIL, GROUP_ID, USER_DATE));


    ALTER TABLE USER_GROUP ADD (
    CONSTRAINT USER_GROUP_FK FOREIGN KEY (EMAIL)
    REFERENCES USERt (EMAIL));

    ALTER TABLE USER_GROUP ADD (
    CONSTRAINT USER_GROUP_FK1 FOREIGN KEY (GROUP_ID)
    REFERENCES GROUPt (GROUP_ID));

    CREATE TABLE USER_ATTRIBUTE
    (
    ID VARCHAR2(20 BYTE) NOT NULL,
    EMAIL VARCHAR2(100 BYTE) NOT NULL,
    TYPE VARCHAR2(100 BYTE) NOT NULL,
    VALUE VARCHAR2(4000 BYTE) NOT NULL,
    USER_DATE DATE NOT NULL
    )

    ALTER TABLE USER_ATTRIBUTE ADD (
    CONSTRAINT USER_ATTRIBUTE_PK PRIMARY KEY (ID));


    ALTER TABLE USER_ATTRIBUTE ADD (
    CONSTRAINT USER_ATTRIBUTE_FK FOREIGN KEY (EMAIL)
    REFERENCES USERt (EMAIL));[/QUOTE]

  7. #7
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Hi poratips, sorry for the delay, but (as usual) have had a bussy monday.. I promise to you I'll play a litle bit with this when I get home. As for your last error, you did make sure you're not inserting duplicate values into any of the keys, right ?

  8. #8
    Join Date
    Dec 2004
    Posts
    19

    MultiInsert

    Thanks.
    In INFO table i am storing email once but in RECEIVE_INFO table Iam storing multiple times and I think my where part causing problem
    FROM INFO A, RECEIVE_INFO B, C
    WHERE A.EMAIL = B.EMAIL
    AND B.RECEIVE_INFO_ID = C.RECEIVE_INFO_ID
    here I want to load email in USERt table once from INFO table but email from RECEIVE_INFO into ATTRIBUTE table as same is repeating for multiple attributes.

  9. #9
    Join Date
    Dec 2004
    Posts
    19

    MultiInsert

    Hi,
    I tried with ROW_NUM partition and it works but having problem with another tables which needs to be more than one emails and those emails have multiple reocrds into another table so I am getting Ref. Intrgrity error: Parent Key not found.
    Appreciated your response.

    Thanks,

  10. #10
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    poratips, this is *huge*. I tried running your script, but they failed. Loose the user table, it conflicts with the user function. A primary key is not null by default, loose the not null constraint in the table declaration.

  11. #11
    Join Date
    Dec 2004
    Posts
    19

    MultiInsert

    I have Usert table and it's works fine when I ran the script.

    Thanks,

  12. #12
    Join Date
    Dec 2004
    Posts
    19
    Hi Martinez,
    Thanks for valuable help.
    in my script it's a USERt table not USER. Please let me know if you want another script with data.

    Thanks,

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •