Text to NVarChar
-
I am trying to write an alter statement that will change a data type of Text to NVarChar. I am getting an error that says it cannot alter column because it is text. Is there another way to do this?
ALTER TABLE Mytable ALTER COLUMN Column1 nvarchar(50) NOT NULL
-
I might be wrong on this, but I suspect this is one of the few occasions where EM comes in handy. IIRC, you can do this in EM table design view. But I hope someone will correct me on this.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I need to change this by script.
-
From BOL (ALTER TABLE)
The altered column cannot be:
A column with a text, image, ntext, or timestamp data type.
You should consider copying the data to a temp table, dropping and creating the current table and moving the data back.
Quand on parle du loup, on en voit la queue
-
You can change this by script. You save the modifications EM is suggesting in the change script and run this whenever you want.
May I say that it is a huge step from TEXT to NVARCHAR(50). It looks as if someone hasn't thought very carefully during design time.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I'll give the EM table modifications a try.
I'm not sure how many characters the nvarchar will be so I put in 50. I just know that a change will be needed from Text to nvarchar at this point.
-
As Frank states the EM method can be scripted, just remember to test the script on a backup of the production database before running on the live system, as in the past I have experienced problems with this method, especially on SQL Server 7.
The script that is generated starts by creating a new table with your new column schema, and copies the existing data into this table. The existing table and relationships (etc.) are dropped, and the new table is renamed to be the same as the original table, and finally the relationships are recreated.
An alternative would be to alter your table and add a temp column of the new type (nvarchar), copy the text data into the nvarchar column, drop the original column, add another nvarchar column which has the same name as the original text column, copy the temp column's data into it, and finally drop the temp column.
You can also use the DATALENGTH function to check the maximum length of the text data (bearing in mind that DATALENGTH counts bytes, not characters), and also identify columns that will be truncated.
Example:-
-- SETUP:
-- Create a test table
CREATE TABLE mytable (
[my_id] int IDENTITY(1,1) PRIMARY KEY CLUSTERED,
[my_info] text)
GO
-- Add some test data
DECLARE @nCounter int
SET @nCounter = 0
WHILE @nCounter < 100
BEGIN
INSERT INTO mytable
( [my_info] )
VALUES( 'Test data row ' + CAST(@nCounter AS nvarchar(2)) )
SET @nCounter = @nCounter + 1
END
GO
-- What is the max length of the text in the my_info column?
SELECT MAX(DATALENGTH([my_info])) FROM mytable
GO
-- Show the columns which will be truncated by this action
SELECT [my_info] FROM mytable
WHERE DATALENGTH([my_info]) > 50
GO
-- CHANGE COLUMN DATA TYPE EXAMPLE:
-- Alter your table to add a temp column with the nvarchar data type
ALTER TABLE mytable
ADD [temp_my_info] nvarchar(50)
GO
-- Copy the data from the text column to the nvarchar column
UPDATE mytable
SET [temp_my_info] = CAST([my_info] AS nvarchar(50))
GO
-- Drop the text column
ALTER TABLE mytable
DROP COLUMN [my_info]
GO
-- Add the nvarchar column using the original text column's name
ALTER TABLE mytable
ADD [my_info] nvarchar(50)
GO
-- Copy the data into the new nvarchar column
UPDATE mytable
SET [my_info] = [temp_my_info]
GO
-- Drop the temp column
ALTER TABLE mytable
DROP COLUMN [temp_my_info]
GO
-- Display the result
SELECT * FROM mytable
GO
-- CLEANUP:
-- Drop the test table
DROP TABLE mytable
GO
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply