This discussion is archived
9 Replies Latest reply: Nov 18, 2012 3:01 AM by jeneesh RSS

Update bulk data on table

Sudhir_Meru Newbie
Currently Being Moderated
Hi,

I have a table which is already existing with data. I got another set of data from spread sheet to update the date on table. Table name is EMPLOYEE.

Table Structure is like this

>> EMP_ID, FIRST_NAME, LAST_NAME, ADDRESS, AGE, PHONE_NUMBER

In table most of the records dont have ADDRESS OR PHONE_NUMBER data, Spread sheet contains the updated information which has to be update on table EMPLOYEE.

Please suggest me some method to update the data.

I am planning to create a temporary table upload the data into the table. and then update the data using tables. Is this right approach please suggest

Thanks
Sudhir
  • 1. Re: Update bulk data on table
    jeneesh Guru
    Currently Being Moderated
    Sudhir_Meru wrote:
    Hi,
    I am planning to create a temporary table upload the data into the table. and then update the data using tables. Is this right approach please suggest
    Looks like the right approach.

    You can define an external table for reading the data in the file (You will have to move the file to DB server).
    if data is not that huge, you can directly UPDATE or MERGE using the external table.
    If data is huge, you could think of loading the exernal table data into a staging table using
    INSERT INTO...SELECT .... FROM and then do MERGE/UPDATE
  • 2. Re: Update bulk data on table
    971895 Journeyer
    Currently Being Moderated
    How many records in spreed sheet?

    use bulk collect to update the employee table..
  • 3. Re: Update bulk data on table
    jeneesh Guru
    Currently Being Moderated
    968892 wrote:
    How many records in spreed sheet?

    use bulk collect to update the employee table..
    Why BULK COLLECT?

    SQL is always faster than PL/SQL...

    No need of bulk collect...
  • 4. Re: Update bulk data on table
    Chanchal Wankhade Journeyer
    Currently Being Moderated
    hi,

    it's correct approach to update the same. but what you are using to uploaded the data into oracle table.
    you can do it by two way i.e. using sql loader and external table.

    After uploading use simple udate statement to update the rows....
  • 5. Re: Update bulk data on table
    971895 Journeyer
    Currently Being Moderated
    based on load ..if you have 10k records use sql else go with bulk collect..
  • 6. Re: Update bulk data on table
    jeneesh Guru
    Currently Being Moderated
    968892 wrote:
    based on load ..if you have 10k records use sql else go with bulk collect..
    What if he has 10k+1 records?

    How did you reach on that number - 10K?
  • 7. Re: Update bulk data on table
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    968892 wrote:
    based on load ..if you have 10k records use sql else go with bulk collect..
    Nonsense.

    Else, if you don't think that statement is nonsense, provide technical justification for it.
  • 8. Re: Update bulk data on table
    Sudhir_Meru Newbie
    Currently Being Moderated
    I Tried using the merge option am getting this error need suggession how to fix this erorr


    MERGE INTO BACKUP_EMPLOYEE_DETAILS BE
    USING MIGRATION_EMP_LIST ME
    ON
    (
    BE.UNIQUE_ID = ME.UNIQUE_ID
    )
    WHEN MATCHED THEN
    UPDATE
    SET
    BE.FIRST_NAME = ME.FIRST_NAME,
    BE.MIDDLE_NAME = ME.MIDDLE_NAME,
    BE.LAST_NAME = ME.LAST_NAME,
    BE.EMAIL_ID_PERSONAL = ME.PERSONAL_EMAIL_ID,
    BE.PERSONAL_EMAIL = ME.PERSONAL_EMAIL_ID,
    BE.PROFESSIONAL_MAIL = ME.PROFESSIONAL_EMAIL_ID,
    BE.MOBILE_NUMBER = ME.EMPLOYEE_CONTACT_NO ,
    BE.CURRENT_VISA_STATUS = ME.VISA_STATUS,
    BE.SOURCED_BY = ME.SOURCED_BY,
    BE.REFERRED_BY = ME.REFERRED_BY,
    BE.EMPLOYEE_AVAILABILITY = ME.EMPLOYEE_STATUS,
    BE.EMPLOYEMENT_TYPE = ME.EMPLOYEE_TYPE,
    BE.COMMENTS = ME.STATUS,
    BE.ADDRESS = ME.CURRENT_ADDRESS ,
    BE.PERMANENT_ADDRESS_US = ME.PERMANENT_ADDRESS
    WHEN NOT MATCHED THEN
    INSERT
    (
    BE.FIRST_NAME,
    BE.MIDDLE_NAME,
    BE.LAST_NAME,
    BE.MOBILE_NUMBER,
    BE.HOME_PH_NUMBER,
    BE.SSN,
    BE.ADDRESS,
    BE.CITY,
    BE.STATE,
    BE.OPT_START_DATE,
    BE.INTERESTED_SKILLSET,
    BE.PREFERRED_LOCATION,
    BE.CURRENT_VISA_STATUS,
    BE.RELOCATION,
    BE.AVAILABILITY,
    BE.TYPE_OF_SERVICE_NEEDED,
    BE.CREATED_BY,
    BE.CREATED_ON,
    BE.MODIFIED_BY,
    BE.MODIFIED_ON,
    BE.UNIQUE_ID,
    BE.EDUCATION,
    BE.SOURCED_BY,
    BE.SOURCE_TYPE,
    BE.REFERRED_BY,
    BE.LEVEL_OF_INTEREST,
    BE.COMMENTS_BY_RECRUITER,
    BE.COMMENTS_BY_PROSPECT_EMP,
    BE.CONTRACT_SIGNED,
    BE.CHECK_ISSUED,
    BE.RECRUITERS_RECOMMENDATION,
    BE.INITIAL_DATE_OF_CONTACT,
    BE.LATEST_DATE_OF_CONTACT,
    BE.COMMUNICATION_SKILL_RATING,
    BE.PRIMARY_REFERRAL,
    BE.COMMENTS_BY_STEVEN,
    BE.EMPLOYEE_STATUS,
    BE.EMAIL_ID_PERSONAL,
    BE.PAYROLL_ENTITY,
    BE.PAYROLL_ISSUES,
    BE.COMMENTS_BY_REVAN,
    BE.BACHELOR_DEGREE_SPECIALIZATION,
    BE.MASTERS_DEGREE_SPECIALIZATION,
    BE.PERSONAL_EMAIL,
    BE.STATUS,
    BE.PROFESSIONAL_MAIL,
    BE.PASSWORD,
    BE.ROLE_ID,
    BE.APPLICATION_NAME,
    BE.ACCOUNT_MANAGER,
    BE.ZIP_CODE,
    BE.BUSINESS_NAME,
    BE.OFFICE_EXTENTION_NUM,
    BE.EMERGENCY_PHONE_NUM ,
    BE.EMPLOYEE_TYPE,
    BE.SALARY_PACKAGE,
    BE.SALARY_PER_YEAR,
    BE.TAXABLE_SALARY,
    BE.EMPLOYEE_AVAILABILITY,
    BE.EMPLOYEMENT_TYPE,
    BE.PERMANENT_ADDRESS_US,
    BE.EMPLOYEE_INDIA_ADDRESS,
    BE.EMPLOYEE_INDIA_PHONE,
    BE.COMMENTS
    )
    VALUES
    (
    ME.FIRST_NAME,
    ME.MIDDLE_NAME,
    ME.LAST_NAME,
    ME.EMPLOYEE_CONTACT_NO,
    NULL,
    NULL,
    ME.CURRENT_ADDRESS,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    ME.VISA_STATUS,
    NULL,
    NULL,
    NULL,
    'SYSTEM',
    SYSDATE,
    NULL,
    NULL,
    ME.UNIQUE_ID,
    NULL,
    ME.SOURCED_BY,
    NULL,
    ME.REFERRED_BY,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    ME.PERSONAL_EMAIL_ID,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    ME.PERSONAL_EMAIL_ID,
    28,
    ME.PROFESSIONAL_EMAIL_ID,
    'welcome',
    53,
    'HORIZONTECHNOL',
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    35,
    NULL,
    NULL,
    NULL,
    ME.EMPLOYEE_STATUS,
    ME.EMPLOYEE_TYPE,
    ME.PERMANENT_ADDRESS,
    NULL,
    NULL,
    ME.STATUS
    );


    Error shows as

    Error report:
    SQL Error: ORA-30926: unable to get a stable set of rows in the source tables
    30926. 00000 - "unable to get a stable set of rows in the source tables"
    *Cause:    A stable set of rows could not be got because of large dml
    activity or a non-deterministic where clause.
    *Action:   Remove any non-deterministic where clauses and reissue the dml.



    Thanks
    Sudhir
  • 9. Re: Update bulk data on table
    jeneesh Guru
    Currently Being Moderated
    That means your UNIQUE_ID is not actually UNIQUE...

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points