INSERT - integer not null PK

goldfishhh · #1 (**permalink**) 06-03-10, 06:10

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

dav1mo · #2 (**permalink**) 06-03-10, 08:02

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

tonkuma · #3 (**permalink**) 06-03-10, 10:38

Quote:

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
;

Internet Services

Web Development

Digital Marketing

Consumer Tech