SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE Test (
    MyCol nvarchar(max) 
);

INSERT INTO Test (MyCol)
VALUES ('asdfasdfasdfasdfasdfasdfasdfasdfadfsasdfadfs');



1 rows affected

DECLARE @len int;
SELECT @len = MAX(LEN(MyCol)) FROM dbo.Test;
--44 characters. Store this as output as variable

DECLARE @sql nvarchar(max) = N'
ALTER TABLE TEST ADD NewMyCol varchar($l$);';

SET @sql = REPLACE(@sql, N'$l$', RTRIM(@len));
EXEC sys.sp_executesql @sql;
UPDATE dbo.Test SET NewMyCol = MyCol;
1 rows affected

SELECT * FROM dbo.Test;


MyCol
NewMyCol


asdfasdfasdfasdfasdfasdfasdfasdfadfsasdfadfs
asdfasdfasdfasdfasdfasdfasdfasdfadfsasdfadfs