Automatically generating Guids in Sybase

Question

I have a table with 42 million records. 32 million have a null value and I would like to generate a new guid for each one. Should I do this in batches?

Also, going forward, I would like a new guid added to the field on the insert of a new record. What is the best way to do this?

The field is not the primary key, which is an auto-incrementing integer.

Answer 1

Have you tried an insert trigger?

Answer 2

There is a newid() function to generate a guid. Your column should be varchar(32) (or varbinary(16)) or bigger. You should run the update in batches to avoid filling up the transaction log e.g

set rowcount 10000
while exists (select 1 from mytab where myid is null)
  update mytab set myid = newid() where myid is null
set rowcount 0

You don't need a trigger - just bind the function as a column default. There is an example in the Sybase docs.