locked
Altering column datatype using Tsql RRS feed

  • Question

  • Environment: SQL Server 2005

    Problem:an error message  occur  in a attempt to alter column data type from VARCHAR(50) to CHAR(9 )using Tsql

    Code

    CREATE TABLE new_employees
    
    (
    
    id_num varchar(50),
    fname varchar (20),
    minit char(1),
    lname varchar(30)
    );
    
     
    
    INSERT new_employees
    
       (id_num,fname, minit, lname)
    
    VALUES
    
       ('123456789','bananna', 'F', 'Lemon');
    
     
    
    INSERT new_employees
    
       (id_num,fname, minit, lname)
    
    VALUES
    
       ('987654321','wakawaka', 'O', 'damnsohot');
    
    ALTER TABLE  new_employees  
    ADD id_num Char(10)
    

    How I would go around this problem and be able to change the data type of the column without losing data.




    • Edited by Sandra VO Monday, August 19, 2013 3:54 PM
    Thursday, August 15, 2013 10:12 PM

Answers

  • From the error message I think you are changing the table using SSMS and not using ALTER TABLE statement. From SQL Server 2005 onwards altering a table structure from SSMS is prevented by default (You can enable this by going to Tools -> Options -> Designers -> then uncheck 'Prevent saving changes that require table re-creation'. But I am not recommending this).

    The proper method is to use an ALTER TABLE script from the query editor.

    ALTER TABLE  new_employees ALTER COLUMN id_num Char(9);


    Krishnakumar S

    • Marked as answer by Sandra VO Monday, August 19, 2013 4:51 PM
    Friday, August 16, 2013 4:22 AM

All replies

  • You need to write the ALTER command as,

    ALTER TABLE new_employees
    ALTER COLUMN id_num Char(9)


    Regards, RSingh

    • Proposed as answer by Kevin Urquhart Friday, August 16, 2013 4:39 AM
    Friday, August 16, 2013 3:29 AM
  • From the error message I think you are changing the table using SSMS and not using ALTER TABLE statement. From SQL Server 2005 onwards altering a table structure from SSMS is prevented by default (You can enable this by going to Tools -> Options -> Designers -> then uncheck 'Prevent saving changes that require table re-creation'. But I am not recommending this).

    The proper method is to use an ALTER TABLE script from the query editor.

    ALTER TABLE  new_employees ALTER COLUMN id_num Char(9);


    Krishnakumar S

    • Marked as answer by Sandra VO Monday, August 19, 2013 4:51 PM
    Friday, August 16, 2013 4:22 AM
  • Environment: SQL Server 2005

    Problem:an error message  occur  in a attempt to alter column data type from VARCHAR(50) to CHAR(9 )using Tsql

    Code

    CREATE TABLE new_employees
    
    (
    
    id_num varchar(50),
    fname varchar (20),
    minit char(1),
    lname varchar(30)
    );
    
     
    
    INSERT new_employees
    
       (id_num,fname, minit, lname)
    
    VALUES
    
       ('123456789','bananna', 'F', 'Lemon');
    
     
    
    INSERT new_employees
    
       (id_num,fname, minit, lname)
    
    VALUES
    
       ('987654321','wakawaka', 'O', 'damnsohot');
    
    ALTER TABLE  new_employees  
    ADD id_num Char(9)
    

    How I would go around this problem and be able to change the data type of the column without losing data.



    Try to

    1. Have a new column with the type of varchar(50), and copy the data of the current column to it

    2. Update the current column to null

    3. Retry what you do


    Many Thanks & Best Regards, Hua Min

    Friday, August 16, 2013 4:29 AM
  • It seems you are doing it via SSMS, try to do it from tsql.

    ALTER TABLE  new_employees ALTER COLUMN id_num Char(9)

    But before alter the column, ensure that columns has all values less than 9 length other wise string truncation issue will occur.

    run below commnd to check the same, to check values greater than 9.

    select * from table where len(id_num) >9


    Regards,
    Rohit Garg
    (My Blog)
    This posting is provided with no warranties and confers no rights.
    Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread.

    Friday, August 16, 2013 5:24 AM