0

I'm pretty good around Oracle but I've been struggling to find a decent solution to a problem I'm having with Sybase.

I have a table which has an IDENTITY column which is also a User Defined Datatype (UDD) "id" which is numeric(10,0). I've decided to replace the UDD with the native datatype but I get an error when I do this.

I've found that the only way to do this is:

  • Rename the original table (table_a to table_a_backup) using the procedure sp_rename
  • Recreate the original table (table_a) but use native data types
  • Copy the contents of the backup table to the original (i.e insert into table_a select * from table_b)

This works however I have over 10M records and it eventually runs out of log segment and halts (I can't increase the segment any more due to physical requirements).

Does anybody have a solution, preferably not a solution which would involve processing the records as anything but one large set?

Cheers,

JLove

raven-king
  • 1,550
  • 2
  • 18
  • 40

1 Answers1

0

conceptually, something like this works (in Sybase ASE 12.5.x) ...

  1. do an "alter table drop column" on your current ID column
  2. do "alter table add column" stmt to add new column (w/ native datatype) with IDENTITY attribute

Note that the ID field might not have the same numbers, so be very wary of doing the above if the ID field is used as an explicit or implicit key to other tables.

Garrett
  • 1,750
  • 2
  • 16
  • 23
  • Or see my answer to [this question](http://stackoverflow.com/questions/9571249/alter-table-set-identity-column) for a way to preserve ID values (basically: add new column, copy into it, drop the old column, rename the new). – lre Feb 14 '14 at 06:46