CODEWITHSUNDEEP
sqlsql-server
user3016270
user3016270

Reputation: 11

error converting data types when importing excel file into sql server

As a SQL beginner, I am trying to import data from Excel to a table in sql server. I imported the data using sql import wizard. Since the wizard always defaut some of my numeric columns into nvarchar and won't allow me to change it in mapping, I planned to import the data into a temp table, then use INSERT with CAST function to transfer the data into the permanent target table. When doing the insert, however, I got the error of 'error converting data type nvarchar to numeric'. anyone can tell me why and how to solve the issue? Here is my code:

INSERT INTO [DatabaseA].[dbo].[mstr_Project]
  ([Project_Start_Year]
  ,[Project_Name]
  ,[Client_Name]
  ,[Client_Revenue_in_Millions]
  ,[Client_Employee_Number])
SELECT [ProjectStartYear]
  ,[ProjectName]
  ,[ClientName]
  ,CAST([ClientRevenuesInMillions] AS NUMERIC)
  ,CAST([EmployeeNo] AS NUMERIC)
FROM [dbo].[temp_ProjectImport]

Thanks a million!!

Upvotes: 0

Views: 1718

Answers (1)

vhadalgi
vhadalgi

Reputation: 7189

you could do this convert(nvarchar(255), @col) 

INSERT INTO [DatabaseA].[dbo].[mstr_Project]
      ([Project_Start_Year]
      ,[Project_Name]
      ,[Client_Name]
      ,[Client_Revenue_in_Millions]
      ,[Client_Employee_Number])
    SELECT [ProjectStartYear]
      ,[ProjectName]
      ,[ClientName]
      ,CAST([ClientRevenuesInMillions] AS NUMERIC)
      ,CONVERT(NVARCHAR(255),[EmployeeNo] ) as new_converted_value
    FROM [dbo].[temp_ProjectImport]

Upvotes: 0

Related Questions