none
How to change a data type in a table in Microsoft SQL Server 2008

    Question

  • Hello Team,

    I have exported a file from Microsoft Excel to SQL Server 2008 R2. I need to change the data type, since Excel doesn't say anything about the data type and every field is exported as a text. I have a field that should have data type as Money.

    I want to convert the data type floating to Money and text into date/ time I can't. I keep receiving this error:

    Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either make changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table to be re-created.

    What is the fix,

    Best,

    GGGGGNNNNN


    GGGGGNNNNN

    Tuesday, June 25, 2013 4:52 AM

Answers

  • Hi,

    All you need to do is:

    • Open SQL Server Management Studio (SSMS).
    • On the Tools menu, click Options.
    • In the navigation pane of the Options window, click Designers.
    • Select or clear the Prevent saving changes that require the table re-creation check box, and then click OK.


    Let me know if you have any more questions on this. Please mark this post as an answer if it helps you resolve your question. - Bhawesh

    Tuesday, June 25, 2013 5:03 AM
  • ALTER TABLE mytable ALTER COLUMN mycolumn newtype

    This script should help you changing data type of a column. But you should be aware that you might run into problem while trying to convert datatypes.

    For example, if you are converting a varchar column to integer, while there are non-numeric data in some rows.


    Let me know if you have any more questions on this. Please mark this post as an answer if it helps you resolve your question. - Bhawesh

    • Proposed as answer by Bhawesh Vithalani Tuesday, June 25, 2013 7:32 AM
    • Marked as answer by GGGGGNNNNN Wednesday, June 26, 2013 8:05 AM
    Tuesday, June 25, 2013 7:32 AM
  • I usually add a new column and issue an update query which populates the new column.

    Then I drop the old column and rename the new column with original name.



    val it: unit=()

    • Marked as answer by GGGGGNNNNN Tuesday, June 25, 2013 5:29 AM
    Tuesday, June 25, 2013 4:59 AM

All replies

  • I usually add a new column and issue an update query which populates the new column.

    Then I drop the old column and rename the new column with original name.



    val it: unit=()

    • Marked as answer by GGGGGNNNNN Tuesday, June 25, 2013 5:29 AM
    Tuesday, June 25, 2013 4:59 AM
  • Hi,

    All you need to do is:

    • Open SQL Server Management Studio (SSMS).
    • On the Tools menu, click Options.
    • In the navigation pane of the Options window, click Designers.
    • Select or clear the Prevent saving changes that require the table re-creation check box, and then click OK.


    Let me know if you have any more questions on this. Please mark this post as an answer if it helps you resolve your question. - Bhawesh

    Tuesday, June 25, 2013 5:03 AM
  • Hello MSDN Student,

    Thank you very much, do you add the column in the design view?

    Can you please tell me what is the script for update query for the changing the field data type?

    How do you drop the old column? Do you delete it?

    Regards,


    GGGGGNNNNN

    Tuesday, June 25, 2013 5:32 AM
  • Hello Bhawesh,

    I am trying to learn SQL, I have lots of questions. I appreciate your response and other team members greatly. Without these members, I wouldn't be learn as much as I have learned now.

    Now, if we are going to use an update query to change the data type of a field, what would be the script for that  update query?

    Also, I am building a dashboard and I have posted some questions, see if you can answer it.

    Regards,

    GGGGGNNNNN


    GGGGGNNNNN

    Tuesday, June 25, 2013 5:39 AM
  • ALTER TABLE mytable ALTER COLUMN mycolumn newtype

    This script should help you changing data type of a column. But you should be aware that you might run into problem while trying to convert datatypes.

    For example, if you are converting a varchar column to integer, while there are non-numeric data in some rows.


    Let me know if you have any more questions on this. Please mark this post as an answer if it helps you resolve your question. - Bhawesh

    • Proposed as answer by Bhawesh Vithalani Tuesday, June 25, 2013 7:32 AM
    • Marked as answer by GGGGGNNNNN Wednesday, June 26, 2013 8:05 AM
    Tuesday, June 25, 2013 7:32 AM