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.

regards

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

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.

thanks

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.

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!