Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2009

    Unanswered: Insert with a subquery (distinct select)

    Hi all,
    I am trying to do a select + insert in a single query and the syntax I think is pretty straight forward:

    Insert into table1 (id,name)
    select * from table2

    Assuming similar data types in both tables this should work. The problem I have is, that in table 2 I do not want all names returned, but only distinct ones. So clearly, I'm wont get 'one' id from table 2.
    Consider a case where table 2 had multiple rows called 'John', I'm ok with any of the IDs of these johns getting returned with the distinct name of john.

    In case the above is not possible, Is there a way to generate timstamp based primary keys in DB2? Since I'll be doing batch inserts, microsecond precision might not work (which I tried using bigint(current_timestamp) and failed). I can only work with bigints as primary keys.

    the main reason I'm trying to do this in a single SQL is to avoid the millions of rows clogging up my java memory if the select and inserts are separated.

    Any help would be appreciated.


  2. #2
    Join Date
    Jun 2003
    Toronto, Canada
    Provided Answers: 1
    Not sure if I understood what you're after, but if you really don't care about the ID value, this should do:

    select name, min(id) from table2 group by name

  3. #3
    Join Date
    Jul 2009
    thanks n_i, that works for me!
    Just curious about my second question though, is there a DB2 method to create a nanosecond precision bigint timestamp? all the other primary keys I have across tables are on similar lines, and when I do a insert with a select subquery, I'm kinda copying an older timestamp.


  4. #4
    Join Date
    Jan 2007
    Jena, Germany
    The CURRENT TIMESTAMP of a single SQL statement is frozen during the statement execution, i.e. if you insert multiple rows, the current timestamp is determined only once and then the value is used everywhere where the special register is being referenced. If this is a problem, you could implement an external UDF in C/C++ or Java, which returns a new timestamp on each invocation.

    Note that if the timestamp shall be guaranteed to be unique, you should either use a single-core, non-partitioned system only, or implement some other mechanism to get unique values. As soon as you have parallelism, using a system timestamp (with whatever precision) cannot be guaranteed to be always unique.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Jul 2009
    thanks stolze,
    I am currently using java to generate my primary keys and I guess I might have to extend that approach as a UDF now.
    thanks for the suggestion!

Posting Permissions

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