How to copy an ntext column's value to a new non-null column

Question

I have a table that has an ntext column defined as [value1] [ntext] NOT NULL. I want to add another ntext column to this table that is basically a copy of this column's values (they don't need to stay in sync). To do this, I am using 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 also work in SQL Server 2000. According to BOL, ALTER TABLE cannot be used on an ntext column in SQL Server 2000. The final alter table is needed because the column must be defined as NOT NULL.

Is there any way to achieve this in SQL Server 2000 without having to make a new table, copy all of the rows across, delete the old table and then rename the new table? The table has a lot of foreign keys and constraints that I don't really want to have to unpick and recreate.

(I'm aware that ntext is deprecated - this is part of a change to a legacy application that has to use them for the time being.)

Answer 1

No offense but you state that value1 is defined as NOT NULL so why do you even need to set the value2 column to allow nulls in the first place. If you must have the column NOT NULL then make it NOT NULL and get rid of the second alter table command all together.

Based on your example you could not have a NULL value anyway.

Now if your source allowed nulls and your new column would not you could just include a where clause in your UPDATE statement to exclude NULL values.

Answer 2

What about using a default value for the value2 column? something like this...

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

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

You can then remove the default constraint if you wish