hi All,,

A table I am working with has a non automatic record number as a primary key. So when some one deletes a record he creates a gap in the record number. It hasnt mattered till now but we are running out of the max limit we decided for the records.

I need to write a querry that copies one users' records to another user in the same table with but different primary keys (generated by another querry). Any ideas how to do it?

Basically it'd be (if there was no primary key involved)
insert into baskets (user_num,company,notes,formula)
select 9113,company,notes,formula from baskets where user_num = 9112.

If transfering from 9112 to 9113. but considering that there is primary key which is called number , how would you do it? The number can be generated with the following querry

select number,(number + 1) from custom_baskets cb1
where
not exists
(select 1 from custom_baskets cb2
where cb2.number = cb1.number + 1)


I'd appreicate any suggestions,

Oh yes and I am using informix and c++ , FYI

Thanks -- Parth