Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2009
    Posts
    4

    Unanswered: sql procedure to insert records to a table

    Hi All,

    I would like to ask for a favour.

    I am new to writing sql procdure.

    I have three tables sax XDF00, YDF00 and ZDF00. I would like to create a sql procedure, which first delete all the records from XDF00 (which will inturn delete all the records from YDF00, which has foreign key relatioinship with XDF00). After deletion, based on the value of a particular field say ref.ZDF00(a sequence possibly, ZDF00 has also foreign key relationship with XDF00) if it exists, we need to insert records in XDF00 for ref.ZDF00 else insert new values.

    Could you please advise?

    Cheers
    coolboy

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I thought that no special difficulty in your requirements.
    It will be enough to DELETE/INSERT accoding to your required sequence.
    I didn't see the neccesity of LOOP, at least from your description.

    Some operations may be simplified by specifying appropriate rules on referential constraint clause.
    For example, rows in YDF00 references XDF00 can be deleted by ON DELETE CASCADE.

    Another example:
    After deletion, based on the value of a particular field say ref.ZDF00(a sequence possibly, ZDF00 has also foreign key relationship with XDF00) if it exists, we need to insert records in XDF00 for ref.ZDF00 else insert new values.
    It may be possible to do by one insert statement for first(if it exists) insert in XDF00. Like this:
    INSERT INTO XDF00
    SELECT ....
    FROM ZDF00
    WHERE ....

    > else insert new values.
    From where do you get the new values?
    Last edited by tonkuma; 11-29-09 at 01:31.

  3. #3
    Join Date
    Nov 2009
    Posts
    4

    sql procedure to insert records to a table

    Hi,

    Many thanks for your reply.

    Initially we were using macro (vb script) in AS400, to update XDF00. The set {x,y,z} where y and z are sequences forms the primary key for XDF00, YDF00 holds the extension details of XDF00. Due to some technical difficulty, we would like to use sql procedure from now on.

    ZDF00 will be updated with a reference to {x,y,z}, when we update a record for {x,y,z} via front end.

    Since some of the entries in XDF00 got corrupted, we would need to delete all the records and reinsert all of them with out affecting the records for {x,y,z} in ZDF00.

    Could you please advise if it is possible using a sql procedure to get the array of {x,y,z} in its proper order from ZDF00? The for each of the distinct {x,y,z} in the array, we would need to make sure records are available in the XDF00. The values for other fields will nee to be manually inserted in to the insert statements.

    Once we have records in XDF00 for all available {x,y,z} in ZDF00 in the proper order, we will need to insert some more records as the following sequences.

    Cheers
    coolboy
    Last edited by coolboy; 11-29-09 at 07:53.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I couldn't understand what you want to do exactly.

    Please give me DDLs, sample data before the procedure run and expected data after the procedure run, for all three tables,
    and parameters of the procedure.

    It is not necessary to describe the process(or logic) in the procedure, at least now.
    In other words, please supply enough sample data to understand what should do in the procedure.

    Are you using DB2 for iSeries? What version/release are you using?

  5. #5
    Join Date
    Nov 2009
    Posts
    4

    sql procedure to insert records to a table

    XDF00 (sample data before deletion)

    X Y Z

    X1 001 1 PAN CANCEL 0
    X2 001 1 RAN BEGIN 0
    X3 001 1 CAN GH_CANCEL 0
    X4 001 1 PAN CANCEL 0
    X5 001 1 RAN BEGIN 0
    X6 001 1 CAN GH_CANCEL 0
    X7 001 2 INM P LIVA05 0
    X8 001 3 INK P KIVA05 0
    X9 001 4 INC P RIVA05 0
    X1 001 9 PAN CANCEL 0
    X2 001 8 RAN BEGIN 0
    X3 001 6 CAN GH_CANCEL 0
    X4 001 6 PAN CANCEL 0
    X5 001 8 RAN BEGIN 0
    X6 001 7 CAN GH_CANCEL 0
    X7 001 7 INM P LIVA05 0
    X8 001 5 INK P KIVA05 0
    X9 001 3 INC P RIVA05 0

    We need to delete all the records from XDF00 as the data is corrupted. We may use delete * from XDF00.

    Now we need to get the distinct occurrences of X,Y,Z in ZDF00 and re-insert records for each X,Y,Z found

    ZDF00 (sample data)

    X Y Z (Foreign Key)

    100,000,003 0 X1 002 9 035236 001
    100,000,004 0 X2 001 2 007369 005
    100,000,005 0 X2 002 6 035236 001
    100,000,006 0 X3 001 3 007369 003
    100,000,007 0 X1 001 9 007369 004
    100,003,229 0 X4 001 1 038994 001
    100,003,230 0 X8 001 6 038995 001
    100,003,231 0 X3 001 5 038995 001
    100,003,232 0 X5 001 10 038995 001
    100,003,239 0 X9 001 3 038998 001
    100,003,240 0 X8 001 7 038998 001
    100,003,241 0 X3 001 8 038998 001
    100,003,244 0 X1 001 6 039001 001
    100,003,245 0 X5 001 4 039001 001
    100,003,246 0 X4 001 10 039001 001

    XDF00 (sample data after insertion based on entries in ZDF00)

    X Y Z

    X4 001 1 - - -
    X2 001 2 - - -
    X3 001 3 - - -
    X9 001 3 - - -
    X5 001 4 - - -
    X3 001 5 - - -
    X2 002 6 - - -
    X8 001 6 - - -
    X1 001 6 - - -
    X8 001 7 - - -
    X3 001 8 - - -
    X1 002 9 - - -
    X1 001 9 - - -
    X5 001 10 - - -
    X4 001 10 - - -


    Newly added

    L3 001 8 - - -
    L1 002 9 - - -
    L1 001 9 - - -
    L5 001 10 - - -
    L4 001 10 - - -
    L5 001 11 - - -

    We are using DB2 for iSeries.



    Cheers
    Coolboy
    Attached Files Attached Files
    Last edited by coolboy; 11-29-09 at 13:55. Reason: adding attachment

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I couldn't see the necessity of more than the following two statements for the procedure.
    Code:
    DELETE * FROM XDF00;
    
    INSERT INTO XDF00(x, y, z)
    SELECT DISTINCT
           x, y, z
      FROM ZDF00;
    There is no implied sequence in DB2 tables.
    For example, I saw the following statement in "Chapter 4. Queries P445" in "DB2 for i5/OS SQL Reference Version 5 Release 4".
    If the ORDER BY clause is not specified, the rows of the result table have an arbitrary order.

  7. #7
    Join Date
    Nov 2009
    Posts
    4

    sql procedure to insert records to a table

    Dear tonkuma,

    Many thanks for your reply. The code was really helpful.

    Cheers
    Coolboy

Tags for this Thread

Posting Permissions

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