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

Good Evening,

Thank you for the suggestion to trace back to the original document. I followed your advice and found that the Value column in original source document, a csv file, had a numerical data type. So I built a smaller table from the same source and imported it into ms sql and, during the process, the numerical data type got changed to a nvarchar data type. So I re-imported the file and changed the data type to bigint and bingo it is now a bigint column.

Your recommendation pointed me in a direction that I would never have thought about on my own. Bear with me for a moment of background. I have worked in Microsoft Access, self taught, for about four years. I am comfortable writing queries in Access's design view. However, as the files got bigger and bigger, Access became a problem. So within the last month or so, I started to learn a bit about ms sql server. I have learned to to sue sql server as the back end of Access which was working until I ran into the data type problem that bough me her.

The source file that I am using has many hundreds of columns. So I learned to convert the wide file to a narrow file using PowerQuery's un-pivot function. After un-pivoting, I would load the table to the data model because it exceeded 1,048 million rows. I would then use DAX studio to export the data model to sql. During this process, the data type problem emerged.

I may be reporting "success" prematurely, but after trying your recommendation it dawned on me to google the guestion could columns be unpivoted in sql? So my next challenge will be to find a query or two that looks like it will un-pivot my table.

No guarantees that I won't be back asking for help, but this approach seems to me like it will avoid the data type issue that brought me here in the first place.

You have been very helpful. Please feel free to share additional thoughts.

Thank you.

Al