How to copy ntext column value to new non-zero column

Question

I have a table with a column ntextdefined as [value1] [ntext] NOT NULL. I want to add another column to this table ntext, which is basically a copy of these column values ​​(they don't need to synchronize). For this, I use the following SQL:

ALTER TABLE [table] ADD [value2] [ntext] NULL
UPDATE [table] SET [value2] = [value1]
ALTER TABLE [table] ALTER COLUMN [value2] [ntext] NOT NULL

This works fine in both SQL Server 2005 and 2008, but I need it to work in SQL Server 2000 as well. According to BOL, ALTER TABLEit cannot be used in a column ntextin SQL Server 2000. The final version of the table is necessary because the column must be defined as NOT NULL.

Is there a way to achieve this in SQL Server 2000 without having to create a new table, copy all the rows to another, delete the old table, and then rename the new table? There are many foreign keys and constraints in the table that I really don't want to unwind and recreate.

(I know that ntext is deprecated - this is part of changing the deprecated application that should use them now.)

Answer 1

How to use default value for value2 column? something like that...

ALTER TABLE [table] 
ADD [value2] [ntext] NOT NULL 
CONSTRAINT df_table_value2 DEFAULT ''

UPDATE [table] SET [value2] = [value1]

Then you can remove the default constraint if you want

Answer 2

, , 1 NOT NULL, value2, . NOT NULL, NOT NULL alter table.

NULL.

, null where UPDATE, NULL.