Results 1 to 13 of 13
  1. #1
    Join Date
    Feb 2010
    Posts
    5

    Unanswered: INSERT from one to another Table

    Hi;
    I have two tables exactly same but in two different DB schema. the Table_1 has a primary key SU_ID which gets assigned by system while submitting the value thru online application. I am doing an INSERT INTO command like:

    INSERT INTO TABLE_1 SELECT * FROM TABLE_2.

    the problem is that TABLE_2 has SU_ID which is unique and TABLE_2 has same value.

    How to resolve this issue? Please advice?

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Why would you want to insert duplicate values into table_1?
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Feb 2010
    Posts
    5
    TABLE_1 has old data, TABLE_2 has the updated datab but the SU_ID is unique to each table and it is primary key

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Please try to explain what is it that you wish to accomplish. Do you want to update rows in table_1 with new values in table_2, or add rows from table_2 to table_1? In the latter case you will have to generate unique key values yourself.
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Feb 2010
    Posts
    5
    Sorry for the confusion. Let me explain what I want.

    I have TABLE_1 which has updated value where SU_ID is primary key. the value in SU_ID is system generated 12 byte number. This table has tons of rows.

    I have another TABLE_2 which has exactly same structure but has different value(old data).

    How come I insert the new data into TABLE_2 from TABLE_1?

    I know that INSERT INTO TABLE_2 SELECT * FROM TABLE_1 will not work because TABLE_2 might have values in SU_ID which may be in TABLE_1.

    I was thinking that if I first find out a Max(SU_ID) in TABLE_2 and then keep increamenting by one for the next row then it may work but I don't know how?

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Code:
    insert into table_2 (su_id, ...) 
    select 
     row_number() over () + <maximum su_id value>, 
     <rest of columns> 
    from table_1
    ---
    "It does not work" is not a valid problem statement.

  7. #7
    Join Date
    Feb 2010
    Posts
    5
    Many Thanks.
    while running my query in Mainframe using QMF tool:

    SELECT
    ROW_NUMBER() OVER () + <MAXIMUM SU_ID>,
    OVERRIDE_FLAG,EFFECTIVE_DATE,END_DATE,PARM_VALUE
    FROM MTVDV1.JOB_PARM_DETAIL;

    it is throwing an SQL error like "SQL error at or before < (line 2, position 25)."


    Can you explain why?

  8. #8
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Could be something like this:

    INSERT INTO TABLE_1
    SELECT * FROM TABLE_2
    where SU_ID not in (select SU_ID from TABLE_1)
    ;
    Lenny

  9. #9
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by asriva26 View Post

    Can you explain why?
    Can you explain why you have replaced "<rest of columns>" with "OVERRIDE_FLAG,EFFECTIVE_DATE,END_DATE,PARM_VALUE" , but only removed "value" from "<maximum su_id value>"?
    ---
    "It does not work" is not a valid problem statement.

  10. #10
    Join Date
    Feb 2010
    Posts
    5
    I thought that "<rest of columns>" should be replaced by the actual columns name in the table. in the same way I wrote for "<maximum su_id value>" and removed "value"

    Is this is a mistake? do I need to run the SQL the way you have written the sql?

  11. #11
    Join Date
    Jul 2009
    Posts
    150

    MERGE instead of INSERT

    Quote Originally Posted by asriva26 View Post
    I thought that "<rest of columns>" should be replaced by the actual columns name in the table. in the same way I wrote for "<maximum su_id value>" and removed "value"

    Is this is a mistake? do I need to run the SQL the way you have written the sql?
    You can use MERGE.

    Cara.

  12. #12
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by asriva26 View Post
    I thought that "<rest of columns>" should be replaced by the actual columns name in the table. in the same way I wrote for "<maximum su_id value>" and removed "value"
    How about replacing "<maximum su_id value>" with the actual maximum su_id value?
    ---
    "It does not work" is not a valid problem statement.

  13. #13
    Join Date
    Apr 2005
    Location
    USA
    Posts
    130
    What is your DB2 version on z/OS? If you are in V9.1 it can be done through merge.

Posting Permissions

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