Professional Web Applications Themes

Multiple insert from previous select - IBM DB2

Hi, I need to do following, if there is any automated process let me know. 1.excute Select on the table (eg, TableA) 2. Insert value to a table with values returned in step 1 with updating only one column. for instance selcect dept, year, catg from tablea where dept = 80 it may return 40 rows with different category. Now we want to insert those 40 rows for dept 81, rest remail same. we DB2 7 MVS version. Presently we do it manually. Any help appreciated. Thanks Basu...

  1. #1

    Default Multiple insert from previous select

    Hi,
    I need to do following, if there is any automated process let me know.

    1.excute Select on the table (eg, TableA)
    2. Insert value to a table with values returned in step 1 with
    updating only one column.

    for instance selcect dept, year, catg from tablea where dept = 80

    it may return 40 rows with different category.

    Now we want to insert those 40 rows for dept 81, rest remail same.

    we DB2 7 MVS version. Presently we do it manually.

    Any help appreciated. Thanks Basu
    Basu Guest

  2. #2

    Default Re: Multiple insert from previous select

    Do you mean something like:
    insert into tableb(dept,year,catg)
    select dept+1,year,catg
    from tablea
    where dept=80
    ;


    Basu wrote:
    >Hi,
    >I need to do following, if there is any automated process let me know.
    >
    >1.excute Select on the table (eg, TableA)
    >2. Insert value to a table with values returned in step 1 with
    >updating only one column.
    >
    >for instance selcect dept, year, catg from tablea where dept = 80
    >
    >it may return 40 rows with different category.
    >
    >Now we want to insert those 40 rows for dept 81, rest remail same.
    >
    >we DB2 7 MVS version. Presently we do it manually.
    >
    >Any help appreciated. Thanks Basu
    >
    sharpe@us.ibm.com Guest

  3. #3

    Default Re: Multiple insert from previous select

    ]""Basu" <basusm> wrote in message
    news:7cd67dce.0306301003.56e39801posting.google.c om...
    > Hi,
    > I need to do following, if there is any automated process let me know.
    >
    > 1.excute Select on the table (eg, TableA)
    > 2. Insert value to a table with values returned in step 1 with
    > updating only one column.
    >
    > for instance selcect dept, year, catg from tablea where dept = 80
    >
    > it may return 40 rows with different category.
    >
    > Now we want to insert those 40 rows for dept 81, rest remail same.
    >
    > we DB2 7 MVS version. Presently we do it manually.
    >
    Thank you for telling us your DB2 Version; a surprising number of people
    post to this newsgroup and don't tell us. That makes it very hard to answer
    the question since the answer often depends on the exact version of DB2 and
    the OS.

    With respect to your question, a trigger is probably what you want. With a
    trigger, an event in one table can cause an event in another table. For
    example, you could set up a trigger so that adding rows to Table A would
    cause rows to be inserted into Table B. You could have another trigger
    insert rows in Table B whenever a row in Table A changed. And so on.

    Please note that this would become an entirely automated process. You would
    not have to remember to do the Select and the Insert every day (or however
    often you do it now.)

    Consult the DB2 manuals, particularly the Application Programming and SQL
    Guide for the concepts and the SQL Reference for the details of the syntax.

    Your challenge will be to rethink what you are doing a little so that you
    realize what event needs to trigger the insert in the second table.


    Rhino


    Rhino Guest

Similar Threads

  1. What's faster - loop for insert or insert...select.
    By mr. modus in forum Coldfusion Database Access
    Replies: 2
    Last Post: December 10th, 01:57 AM
  2. Multiple Insert or Looping Insert
    By payado in forum Dreamweaver AppDev
    Replies: 1
    Last Post: May 25th, 09:33 PM
  3. Replies: 0
    Last Post: September 24th, 03:24 AM
  4. Replies: 0
    Last Post: September 11th, 11:26 AM
  5. Replies: 0
    Last Post: September 11th, 12:19 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139