Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2012
    Posts
    4

    Unanswered: how to insert rows in multiple tables in DB2?

    how to insert rows in multiple tables in DB2 by using single query?

    any attempt will be appreciable



    with regards
    kevin

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    probably by use of a trigger..
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by kevin87 View Post
    how to insert rows in multiple tables in DB2 by using single query?

    any attempt will be appreciable



    with regards
    kevin
    Why is several insert queries a problem? That said you can use a trick with CTE's to accomplish a single statement that manipulates several tables like:

    with T_1 (n) as ( select count(1) from final table ( insert into T1 (...) values (....) ) )
    , T_2 (n) as ( select count(1) from final table ( insert into T2 (...) values (....) ) )
    select 'T_1', n from T_1 union select 'T_2', n from T_2

    I would not use such construct myself, but that's another story
    --
    Lennart

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Consider using INSTEAD OF triggers on a view.

    Using stored procedure could be another alternative.

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    Is there some business reason to do this in one query?

    If not, maybe a more traditional approach would be better.

    Keep in mind this has to be maintained as well as simply implemented . . .

  6. #6
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Arrow

    Quote Originally Posted by papadi View Post
    Is there some business reason to do this in one query?

    If not, maybe a more traditional approach would be better.

    Keep in mind this has to be maintained as well as simply implemented . . .
    This is just a spec. You have to find solution. Nothing else.

    Lenny

  7. #7
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Quote Originally Posted by lelle12 View Post
    Why is several insert queries a problem? That said you can use a trick with CTE's to accomplish a single statement that manipulates several tables like:

    with T_1 (n) as ( select count(1) from final table ( insert into T1 (...) values (....) ) )
    , T_2 (n) as ( select count(1) from final table ( insert into T2 (...) values (....) ) )
    select 'T_1', n from T_1 union select 'T_2', n from T_2

    I would not use such construct myself, but that's another story
    I believe this solution is good. Much better than trigger.
    With view it will not work, if we have joined tables.
    Instead of stored procedure we have to use function, but this one is real single query solution.
    Lenny

  8. #8
    Join Date
    Nov 2012
    Posts
    4
    Thanks to you all guys..

Posting Permissions

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