Alter table add column when select into option is disabled
jonellklibbe
Sybase database disabled, because we have replication
enabled.
This means that we are unable to add new columns to a table
by using the "Alter table add column" statement.
As a work around we are create a temp table, import the
original data into the temp table, drop the original table,
recreate the original altered table and import the data back
in from the temp table.
This was working fine, until we switched from table locking
to row locking. When this statement was run as a script, we
lost 95% percent of our data, because the original table was
dropped before the all the rows were imported to the temp
table.
My questions are:
1. Is it possible to have the "Select Into" option enabled
without affecting the replication?
2. Is there a different way of adding a column to a table if
the "Select Into" can not be enabled on a database level
without having to drop and recreate a table?
Manish Negandhi
Firstly, you need to turn on "Select into" only if you are going to
add non null column. Enabling "Select Into" option does not affect
replication. But you might not replicate some data if there was some
non- logged operation. If your business allows, add colums during
maint window or if it is possible to keep db in 'dbo use' while
adding columns, do so.
You can also turn on "select into" only during the duration when you
add column , disable "select into", once you are done, verify if
tranlog dump is working fine.
-HTH
Manish Negandhi
[TeamSybase]