Change Data Type from NVARCHAR TO BIGINT

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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

Answer 5

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