Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2004
    Posts
    54

    Unanswered: INSERT - integer not null PK

    I'm trying to do an insert of some new records and my source is a flat text file that is length delimited. It contains one record per row except for the primary key field, an integer which is not null and PK.

    I'm trying to do something like this:

    Code:
    INSERT INTO my.desttable
    
    SELECT
      (SELECT max(id) FROM my.desttable) +1           -- id,
    , col1                                                            -- name,
    , col2                                                            -- age
    
    FROM
      my.source
    What happens is max(id) always ends up being the same thing and I get a duplicate number error.

    I've also tried to timestamp() and then pull out just the numbers, but that results in the same timestamp().

    Where am I going wrong?

    DB2 v9.1 on zOS

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Its the same unit of work so the max number will be the same for all in that select. You should look at either sequences or identity column.
    Dave

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I'm trying to do an insert of some new records and my source is a flat text file that is length delimited.
    It contains one record per row except for the primary key field, an integer which is not null and PK.
    I couldn't understand well the explanation.
    One reason might be inappropriate words(e.g. record, flat text file) were used in the explanation.
    Another reason might be my poor English capability.
    (What is "It"? my.source? or my.desttable?)

    So, I'll ignore that.

    Looking into the Code, it would be changed like this:
    Code:
    INSERT INTO my.desttable
    SELECT
           (SELECT max(id) FROM my.desttable)
           + rn
         , col1
         , col2
      FROM
           (SELECT
                   col1
                 , col2
                 , ROW_NUMBER() OVER() AS rn
              FROM
                   my.source
           ) q
    ;

Posting Permissions

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