Results 1 to 7 of 7

Thread: Merging Tables

  1. #1
    Join Date
    Jan 2004
    Location
    karachi
    Posts
    13

    Unanswered: Merging Tables

    Hi,
    I want to merge two different tables as "employees" and "departments" default under hr user, is it possible to merge two tables with different columns my query is below.
    Thanks in advance.

    MERGE INTO departments d
    USING employees e
    ON (d.department_id= e.department_id)

    WHEN MATCHED THEN
    UPDATE SET
    d.manager_id=e.manager_id

    WHEN NOT MATCHED THEN

    INSERT VALUES (e.employee_id,e.department_id,e.salary,
    e.hire_date,e.job_id,e.first_name,
    e.last_name,e.email,e.phone_number,
    e.commission,e.manager_id)
    /
    ske

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Merging Tables

    You seem to be mixing things up somewhat. The statement begins "MERGE INTO departments", but the WHEN NOT MATCHED section appears to be trying to insert a duplicate record into the employee table, using the values found in the employee table! In fact, it will try to insert those values into the department table, and will presumably fail (unless your department table has those columns).

    Perhaps if you explain what you are trying to achieve (rather than how you are trying to achieve it) someone can suggest the way.

  3. #3
    Join Date
    Jan 2004
    Location
    karachi
    Posts
    13

    Question what I am trying to achieve

    Hi, andrewst

    I want to Merge departments and employees tables, as you know the departments and employees have two same columns, is it possible to merge two differnt tables with different columns.
    Thanks
    ske

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: what I am trying to achieve

    Sorry, but I really don't follow your intentions here. Please show me an example of data before and after the merge. Or use the standard EMP and DEPT tables:

    PHP Code:
    SQLselect from emp;

         
    EMPNO ENAME      JOB              MGR HIREDATE           SAL       COMM     DEPTNO
    ---------- ---------- --------- ---------- ----------- ---------- ---------- ----------
          
    7369 SMITH      CLERK           7902 17-DEC-1980        800                    20
          7499 ALLEN      SALESMAN        7698 20
    -FEB-1981       1600        300         30
          7521 WARD       SALESMAN        7698 22
    -FEB-1981       1250        500         30
          7566 JONES      MANAGER         7839 02
    -APR-1981       2975                    20
          7654 MARTIN     SALESMAN        7698 28
    -SEP-1981       1250       1400         30
          7698 BLAKE      MANAGER         7839 01
    -MAY-1981       2850                    30
          7782 CLARK      MANAGER         7839 09
    -JUN-1981       2450                    10
          7788 SCOTT      ANALYST         7566 19
    -APR-2087       3000                    20
          7839 KING       PRESIDENT            17
    -NOV-1981       5000                    10
          7844 TURNER     SALESMAN        7698 08
    -SEP-1981       1500          0         30
          7876 ADAMS      CLERK           7788 23
    -MAY-2087       1100                    20
          7900 JAMES      CLERK           7698 03
    -DEC-1981        950                    30
          7902 FORD       ANALYST         7566 03
    -DEC-1981       3000                    20
          7934 MILLER     CLERK           7782 23
    -JAN-1982       1300                    10

    15 rows selected
    .

    SQLselect from dept;

        
    DEPTNO DNAME          LOC
    ---------- -------------- -------------
            
    10 ACCOUNTING     NEW YORK
            20 RESEARCH       DALLAS
            30 SALES          CHICAGO
            40 OPERATIONS     BOSTON 
    What would they look like after your merge?

  5. #5
    Join Date
    Jan 2004
    Location
    karachi
    Posts
    13

    MY Intention

    Hi,
    I want to Merge employees and departments available under "hr" user
    where employees have these columns

    SQL> desc employees
    Name Null? T ype
    ----------------------------------------- -------- - ---------------
    EMPLOYEE_ID NOT NULL NUMBER(6)
    FIRST_NAME VARCHAR2(20)
    LAST_NAME NOT NULL VARCHAR2(25)
    EMAIL NOT NULL VARCHAR2(25)
    PHONE_NUMBER VARCHAR2(20)
    HIRE_DATE NOT NULL DATE
    JOB_ID NOT NULL VARCHAR2(10)
    SALARY NUMBER(8,2)
    COMMISSION_PCT NUMBER(2,2)
    MANAGER_ID NUMBER(6)
    DEPARTMENT_ID NUMBER(4)

    and departments have

    SQL> desc employees
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    DEPARTMENT_ID NOT NULL NUMBER(4)
    DEPARTMENT_NAME NOT NULL VARCHAR2(30)
    MANAGER_ID NUMBER(6)
    LOCATION_ID NUMBER(4)


    I want to produce an other table in which all columns of departments table merge into employees and same columns repalced with its corresponding column like department_id of departments table is replaced with department_id of employees table.
    Thanks
    Last edited by century2001us; 02-19-04 at 02:38.
    ske

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: MY Intention

    What would that look like with some sample data values? Sorry but I'm just not following this at all at the moment.

    Show me 2-3 rows of sample employee data and 2-3 rows of sample department data, and then show me the desired result of the "merge".

  7. #7
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171
    If you want to create a new table ... are you looking for something like

    create table new_table
    select
    e.employee_id,
    e.department_id,
    ...
    e.manager_id,
    d.department_name
    from emp e, dept d
    where
    e.department_id=d.department_id(+)

    the output of this will be like this:

    Code:
    SQL> select
      2  EMPLOYEE_ID ,
      3  MANAGER_ID ,
      4  DEPARTMENT_ID
      5  from employees;
    
    EMPLOYEE_ID MANAGER_ID DEPARTMENT_ID
    ----------- ---------- -------------
              1          5            50
              2          5            60
              3          5            70
    
    
    SQL> select department_id, manager_id, department_name from departments;
    
    DEPARTMENT_ID MANAGER_ID DEPARTMENT_NAME
    ------------- ---------- ------------------------------
               50          5 accounts
               60          4 admin
    
    
    SQL> select
      2  e.employee_id,
      3  e.department_id,
      4  e.manager_id,
      5  d.department_name
      6  from employees e, departments d
      7  where
      8  e.department_id=d.department_id(+);
    
    EMPLOYEE_ID DEPARTMENT_ID MANAGER_ID DEPARTMENT_NAME
    ----------- ------------- ---------- ------------------------------
              1            50          5 accounts
              2            60          5 admin
              3            70          5
    As you can see, this selects all records from ur employees tables and merges it will department data...

    if this is not what you want, reply back ...
    Oracle can do wonders !

Posting Permissions

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