Generate primary key ids on insert?

[Jim Murphy <ji...@pobox.com>]

I'm writing a transformation script to migrate older DB instances to our new
schema.  PArt of that requires inserting data from one table into another:

insert into foo select a,b,c from bar

My touble is that foo has a primary key column 'id' that needs id values
set.  I have NOT parked this calumn as auto incrementing since when the db
is really in use the incrementing is done by hibernate and the state stored
int he hibernate_unique_key tabel that holds the current hi value.

Anyone know of some modification to the insert statement that would allow me
to generate an integer?  I know the valid ranges, I just need to be able to
insert a different valid int for each row.  This might be a stupid question
that is solved with simple sql - but this is not my forte so hopefully I'm
missing the easy answer.

FWIW, I'm toyed with the idea of altering the table's ID column (add
increment by) for the transform then alter it back but there doesn't seem to
be a way to "drop" increment by - but please correct me if I'm wrong on
that.

-- 
View this message in context: http://www.nabble.com/Generate-primary-key-ids-on-insert--tp14775039p14775039.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.

Re: Generate primary key ids on insert?

[Jim Murphy <ji...@pobox.com>]

Thanks Ryan I'm on it. I came to think that this is the simplest approach too
and appreciate your help!

Cheers,

Jim


Bryan Pendleton wrote:
> 
>> set.  I have NOT parked this calumn as auto incrementing since when the
>> db
>> is really in use the incrementing is done by hibernate 
> 
> If it's just a one-time-load scenario, then one possibility is:
> 
>   - Create a temporary table of the right structure, with the PK as
>     an autoincrementing identity column.
>   - Insert the data into that table.
>   - create the real table with the PK as a non-automatic column (to
>     be maintained by Hibernate)
>   - copy the data from the temp table to the real table using
>     INSERT ... SELECT ...
>   - drop the temp table and run with Hibernate.
> 
> thanks,
> 
> bryan
> 
> 
> 
> 

-- 
View this message in context: http://www.nabble.com/Generate-primary-key-ids-on-insert--tp14775039p14775284.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.

Re: Generate primary key ids on insert?

[Bryan Pendleton <bp...@amberpoint.com>]

> set.  I have NOT parked this calumn as auto incrementing since when the db
> is really in use the incrementing is done by hibernate 

If it's just a one-time-load scenario, then one possibility is:

  - Create a temporary table of the right structure, with the PK as
    an autoincrementing identity column.
  - Insert the data into that table.
  - create the real table with the PK as a non-automatic column (to
    be maintained by Hibernate)
  - copy the data from the temp table to the real table using
    INSERT ... SELECT ...
  - drop the temp table and run with Hibernate.

thanks,

bryan