Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Arrow Unanswered: Urgent Insert statement or procedure

    Hi
    I have a pressing need to write an insert statement, or probably even a procedure (if necessary).

    I have an employee table with two fields (empid and deptid) TABLE1

    I have another table with 3 fields (docid, empid and dt_read). TABLE2

    empid : employeeid, deptid : departmentid, docid : documentid

    Here is the deal. I have to write a SQL statment which would be given a deptid, and it looks at TABLE1 for all empids belonging to that deptid, and inserts rows in the TABLE2

    EMPID DEPTID
    1 2
    1 3
    2 2
    3 2

    suppose I pass deptid = 2, then the query should make 3 inserts for empids = 1, 2 and 3.

    It is really pressing as of now.
    Thanx and Regards
    Aruneesh
    Aruneesh

  2. #2
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Arrow Please confirm if following query would work

    INSERT ALL
    INTO TABLE2 VALUES(3,employeeid)
    SELECT employee_id EMPID from TABLE1

    Thanx and Regards
    Aruneesh

  3. #3
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    One more condition

    I got a part of the query to work like

    insert into TABLE2 (docid, empid) select 3, empid from TABLE1 where deptid = 2 where docid=34;


    Now I have another constraint here, that docid and empid pair should be unique. i.e. if an entry with same docid and empid already exists, it should not insert that particular row but proceed with the rest.

    Thanx and Regards
    Aruneesh

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

    Re: One more condition

    Originally posted by aruneeshsalhotr
    I got a part of the query to work like

    insert into TABLE2 (docid, empid) select 3, empid from TABLE1 where deptid = 2 where docid=34;


    Now I have another constraint here, that docid and empid pair should be unique. i.e. if an entry with same docid and empid already exists, it should not insert that particular row but proceed with the rest.

    Thanx and Regards
    Aruneesh
    Something like this:

    insert into TABLE2 (docid, empid)
    select 3, empid from TABLE1
    where deptid = 2
    and docid=34
    and not exists
    ( select 1 from table2
    where docid=3
    and empid=table1.empid
    );

  5. #5
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Thanx Andrews

    Hi
    I found a work around yesterday itself. I have now also included the SQL you have sent in my code.
    Basically the purpose was, when a document was getting activated, we were populating a table. Now if the document, was deactivated and then reactivated, there would have been a possible duplication of entries. Thats why I need that extra condition.
    So what I did was to flush out the entries on deactivation itself.

    Good that I have have an extra check on insertions which is always good.
    Thanx and Regards
    Aruneesh

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    yeah, with a Procedure you could handle that with a couple cursors.

    Otherwise the SQL provided by Andrew should work fine.

    the checking cursor would be:

    PHP Code:
    cursor cCHECK is
      select emp_id 
        from table_2
          where docid
    v_docid
             
    and empidv_empid;

    BEGIN

    -- open your first cursor to get the emp_ids you want for the new doc_id

        open cCHECK
    ;
           
    fetch cCHECK into v_check_emp_id;
             if 
    cCHECK%notfound then

                insert into table2 
    (
                       
    docid
                       
    empid)
                    
    values (
                       
    v_docid,
                       
    v_empid );
             
              
    end if;
          
    close cCHECK;

    --- Use 
    your loop to get next empid with the new docid 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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