Change Data Type from NVARCHAR TO BIGINT

Al C 41 Reputation points
2022-04-12T23:33:03.51+00:00

Good Afternoon,

I am a true newbie to MS SQL Server. Recently, I imported a file into MS SQL only to find that the data type for my Value column got changed to nvarchar, null. I spent most of today trying proposed solutions found on the internet with no success. The two categories of solutions involve using ALTER, CAST,MODIFY and creating a new column with the BIGINT data type and copying existing data into it. I have not been successful with either of these approaches. Here is what my table looks like:

SELECT TOP (1000) [prvdr_num]
,[hosp_name_hcr]
,[state_abbreviation]
,[health_sys_id]
,[health_sys_name]
,[health_sys_city]
,[health_sys_state]
,[ffy]
,[Attribute]
,[Value]

I would appreciate any help that you can provide to change the data type from NVARCHAR to BIGINT.

Thank you.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,107 questions
SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,256 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,582 questions
{count} votes

6 answers

Sort by: Most helpful
  1. LiHong-MSFT 10,046 Reputation points
    2022-04-13T01:51:22.527+00:00

    Hi @Al C

    I have not been successful with either of these approaches

    Is there any error messages?

    Have you tried TRY_CONVERT:

    UPDATE dbo.YourTable  
    SET BigIntColumn = TRY_CONVERT(BIGINT, Value)  
    

    Or have a try on TRY_PARSE:

    UPDATE dbo.YourTable  
        SET BigIntColumn = TRY_PARSE(Value as BIGINT)  
    

    Best regards,
    LiHong


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

  2. Al C 41 Reputation points
    2022-04-13T02:17:46.267+00:00

    Good Evening,

    Thank you for responding. I will try to answer your questions and hopefully avoid newbie mistakes. I did not create the table in mssql. Rather, I exported the table from the data model using DAX Studio to MSSQL Server. Before loading to the Data Model and subsequently to DAX, the Value column which is the problem, had a numerical data type. When I opened the table in MSSQL it had the NVARCHAR data type.

    Hope the preceding is helpful.

    Thank you.

    192486-mssql-ex.pngio.

    0 comments No comments

  3. Al C 41 Reputation points
    2022-04-13T02:22:32.317+00:00

    Good Evening,

    Thank you for responding and my apologies for hitting the answer button prematurely. This is my first time on this website. I tried both convert and parse but, as I recall, not in the format you have suggested. I am really very new at MSSQL. I have attached a sample copy of my table which I imported to MSSQL.

    Would very much appreciate any thoughts that you might have regarding this question.

    Thank you for responding.

    192543-mssql-ex.png


  4. Tom Cooper 8,466 Reputation points
    2022-04-13T03:23:30.957+00:00

    If @LiHong-MSFT suggestion of
    Alter Table TableName Alter Column Value Bigint
    (of course replace TableName with the name of your table) does not work, there may be rows in your data where the Value column contains data which cannot be converted to an integer. To see if there are any such rows, you can run the query

    Select *  
    From TableName  
    Where Try_Cast(Value As bigint) Is Null And Value Is Not Null  
    

    If that query returns any rows you will have to correct those rows before you can change the column type to bigint.

    Tom

    0 comments No comments

  5. Al C 41 Reputation points
    2022-04-13T11:03:33.347+00:00

    Good Morning,

    Thank you to Martin Cairney, LiHong, and Tom Cooper for offering suggestions to help me better understand next steps. I have posted answers to questions, hopefully, and requests for data. As of last evening, I have tried the following suggestions with no luck:

    • Convert & parse query: didn't record error message
    • Alter query returned the following error message: Msg 8114, Level 16, State 5, Line 1 Error converting data type varchar to bigint.
    • Try_Cast Query

    The Try_Cast query get me closer to understanding next steps. The original file that I imported has 2.3 million rows. The Try_Cast query returned 2.01 million rows which I am assuming means that there are 2.01 million items in the Value column of my table that need to be changed before I can change the data type for the Value column to BIGINT.

    Thank you for your help so far. Would appreciate additional suggestions.

    Al