June 15, 2017 at 7:27 am
How to update column type VARCHAR to NVARCHAR ?
I have data in VARCHAR column.
I need a script which would update the column type VARCHAR to NVARCHAR . It should safely convert column type only . ....should not corrupt the existing data.
Is there any way ?
June 15, 2017 at 7:33 am
spectra - Thursday, June 15, 2017 7:27 AMHow to update column type VARCHAR to NVARCHAR ?I have data in VARCHAR column.
I need a script which would update the column type VARCHAR to NVARCHAR . It should safely convert column type only . ....should not corrupt the existing data.Is there any way ?
Pretty straight forward. You use ALTER TABLE for this.
https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql
ALTER TABLE MyTable
ALTER COLUMN MyColumn nvarchar(100 or whatever) NULL or NOT NULL
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 15, 2017 at 7:36 am
spectra - Thursday, June 15, 2017 7:27 AMHow to update column type VARCHAR to NVARCHAR ?I have data in VARCHAR column.
I need a script which would update the column type VARCHAR to NVARCHAR . It should safely convert column type only . ....should not corrupt the existing data.Is there any way ?
What is the originating varchar column definition, that is varchar(what?)
😎
June 15, 2017 at 7:39 am
From, memory, this, so look up the syntax yourself if it's not quite correct. Change n for the length of your string, and remove the NOT if your column is nullable. If you have any indexes, keys or constraints on the column, you'll need to remove them first and recreate them afterwards.
ALTER TABLE MyTable
ALTER COLUMN MyColumn nvarchar(n) NOT NULL;
John
June 15, 2017 at 8:26 am
If you want to stay safe then check the the actual length of the values, here is an example
😎IF OBJECT_ID(N'DBO.TXX') IS NOT NULL DROP TABLE DBO.TXX;
CREATE TABLE DBO.TXX (X VARCHAR(4001))
INSERT INTO DBO.TXX(X) VALUES (REPLICATE('X',4001))
IF (SELECT MAX(LEN(T.X)) FROM DBO.TXX T) > 4000
BEGIN
ALTER TABLE DBO.TXX ALTER COLUMN X NVARCHAR(MAX);
END
ELSE
BEGIN
DECLARE @TX NVARCHAR(MAX) = CONCAT('ALTER TABLE DBO.TXX ALTER COLUMN X NVARCHAR(',(SELECT MAX(LEN(T.X)) FROM DBO.TXX T),N');');
EXEC ( @TX );
END
June 15, 2017 at 8:39 am
Eirikur Eiriksson - Thursday, June 15, 2017 7:36 AMspectra - Thursday, June 15, 2017 7:27 AMHow to update column type VARCHAR to NVARCHAR ?I have data in VARCHAR column.
I need a script which would update the column type VARCHAR to NVARCHAR . It should safely convert column type only . ....should not corrupt the existing data.Is there any way ?
What is the originating varchar column definition, that is varchar(what?)
😎
original varchar(100),null.
Should I do nvarchar(100),null ....is it safe ?
June 15, 2017 at 8:49 am
spectra - Thursday, June 15, 2017 8:39 AMEirikur Eiriksson - Thursday, June 15, 2017 7:36 AMspectra - Thursday, June 15, 2017 7:27 AMHow to update column type VARCHAR to NVARCHAR ?I have data in VARCHAR column.
I need a script which would update the column type VARCHAR to NVARCHAR . It should safely convert column type only . ....should not corrupt the existing data.Is there any way ?
What is the originating varchar column definition, that is varchar(what?)
😎original varchar(100),null.
Should I do nvarchar(100),null ....is it safe ?
You are testing this on a test database right??? Yes it would be the same size unless you need to change the size also.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply