9 Replies Latest reply: Nov 18, 2012 5:01 AM by jeneesh RSS

    Update bulk data on table

    Sudhir_Meru
      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
          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
            How many records in spreed sheet?

            use bulk collect to update the employee table..
            • 3. Re: Update bulk data on table
              jeneesh
              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
                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
                  based on load ..if you have 10k records use sql else go with bulk collect..
                  • 6. Re: Update bulk data on table
                    jeneesh
                    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
                      Billy~Verreynne
                      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
                        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
                          That means your UNIQUE_ID is not actually UNIQUE...