SQL INSERT INTO SELECT error converting data type nvarchar to float

0
votes

I am currently trying to take data from one table and insert it into another based on a number of constraints. Unfortunately when I run the script I get the following error:

error converting data type nvarchar to float

and I'm unsure how I can change the query to fix this. Both the source and destination tables have all fields as nvarchar(255) and I don't quite know what to do.

The SQL query is as follows:

Insert into Database.dbo.ML_Records ([AccountNumber]
  ,[House Number]
  ,[Street]
  ,[City]
  ,[Postalcode]
  ,[County]
  ,[Phone]
  ,[Country]
  ,[Account Type]
  ,[Nr_of_Dependents]
  ,[FirstName]
  ,[LasName]
  ,[Title]
  ,[JobTitle]
  ,[Email]
  ,[LeadType]
  ,[LeadSource]
  ,[LeadOrigin]
  ,[CampaignCode]
  ,[Contact_Preference]
  ,[Product_Interest]
  ,[NewServicesForCustomer]
  ,[Lead_Description])

Select  [Account Number]  as [AccountNumber]
    , NULL      as [House Number]
    , [Address Line 1] + ' ' + [Address Line 2]                                                                                                                                                     as [Street]
    , Town                                                                                                                                                                                          as [City]
    , Postcode                                                                                                                                                                                      as [Postalcode]
    , County                                                                                                                                                                                        as [County]
    , case when [Telephone] is null then [Mobile] else [Telephone] end                                                                                                                              as [Phone]
    , NULL                                                                                                                                                                                          as [Country]
    , case when [Account Type 2] is not null and [Identical Accounts] = 'No' then [Account Type 2] else [Account Type 1] end                        as [Account Type]
    , [Number of Dependents]                                        as [Nr_of_Employees_Company_Modelled]
    , [Forename]                                                                                                                                                                                    as [FirstName]
    , [Surname]                                                                                                                                                                                     as [LastName]
    , [Title]                                                                                                                                                                                       as [Title]
    , [Job Title]                                                                                                                                                                                   as [JobTitle]
    , case when [Email] is null then [Site Email] else [Email]  end                                                                                                                                 as [Email]
    , NULL                                                                                                                                                                                          as [LeadType]
    , NULL                                                                                                                                                                                          as [LeadSource]
    , NULL                                                                                                                                                                                          as [LeadOrigin]
    , case when ((([Email] is not NULL or [Site Email] is not null) and [Do NOT EMAIL] <> 'Y') and (([Telephone] is not null or [Mobile] is not null) and [DO NOT TELEPHONE] <> 'Y' and [Records on TPS] is NULL and [Records on CTPS] is NULL)) and [CAT Status] = 0 then 'A-B2F-CL-TM/EM-M2-CAT0-GL'
            when ((([Email] is not NULL or [Site Email] is not null) and [Do NOT EMAIL] <> 'Y') and (([Telephone] is not null or [Mobile] is not null) and [DO NOT TELEPHONE] <> 'Y' and [Records on TPS] is NULL and [Records on CTPS] is NULL)) and [CAT Status] = 1 then 'A-B2F-CL-TM/EM-M2-CAT1-GL'
            when ((([Email] is not NULL or [Site Email] is not null) and [Do NOT EMAIL] <> 'Y') and (([Telephone] is not null or [Mobile] is not null) and [DO NOT TELEPHONE] <> 'Y' and [Records on TPS] is NULL and [Records on CTPS] is NULL)) and [CAT Status] = 2 then 'A-B2F-CL-TM/EM-M2-CAT2-GL'
            when ((([Email] is not NULL or [Site Email] is not NULL) AND [Do NOT EMAIL] <> 'Y') and (([Telephone]  is NULL AND [Mobile] is NULL) OR [DO NOT TELEPHONE] = 'Y')) and [CAT Status] = 0 then 'A-B2F-CL-EM-M2-CAT0-GL'
            when ((([Email] is not NULL or [Site Email] is not NULL) AND [Do NOT EMAIL] <> 'Y') and (([Telephone]  is NULL AND [Mobile] is NULL) OR [DO NOT TELEPHONE] = 'Y')) and [CAT Status] = 1 then 'A-B2F-CL-EM-M2-CAT1-GL'
            when ((([Email] is not NULL or [Site Email] is not NULL) AND [Do NOT EMAIL] <> 'Y') and (([Telephone]  is NULL AND [Mobile] is NULL) OR [DO NOT TELEPHONE] = 'Y')) and [CAT Status] = 2 then 'A-B2F-CL-EM-M2-CAT2-GL'
            when ((([Email] is NULL and [Site Email] is null) or [Do NOT EMAIL] = 'Y') and (([Telephone] is not null or [Mobile] is not null) and [DO NOT TELEPHONE] <> 'Y' and [Records on TPS] is NULL and [Records on CTPS] is NULL)) and [CAT Status] = 0 then 'A-B2F-CL-TM-M2-CAT0-GL'
            when ((([Email] is NULL and [Site Email] is null) or [Do NOT EMAIL] = 'Y') and (([Telephone] is not null or [Mobile] is not null) and [DO NOT TELEPHONE] <> 'Y' and [Records on TPS] is NULL and [Records on CTPS] is NULL)) and [CAT Status] = 1 then 'A-B2F-CL-TM-M2-CAT1-GL'
            when ((([Email] is NULL and [Site Email] is null) or [Do NOT EMAIL] = 'Y') and (([Telephone] is not null or [Mobile] is not null) and [DO NOT TELEPHONE] <> 'Y' and [Records on TPS] is NULL and [Records on CTPS] is NULL)) and [CAT Status] = 2 then 'A-B2F-CL-TM-M2-CAT2-GL'
            when ((([Email] is NULL and [Site Email] is null) or [Do NOT EMAIL] = 'Y') and ((([Telephone] is NULL OR [DO NOT TELEPHONE] = 'Y') OR ([Records on TPS] = '1' or [Records on CTPS] = '1')) AND (([Mobile] is NULL OR [DO NOT TELEPHONE] = 'Y') OR ([Records on TPS] = '1' or [Records on CTPS] = '1')))) and [CAT Status] = 0 then 'A-B2F-CL-DM-M2-CAT0-GL'
            when ((([Email] is NULL and [Site Email] is null) or [Do NOT EMAIL] = 'Y') and ((([Telephone] is NULL OR [DO NOT TELEPHONE] = 'Y') OR ([Records on TPS] = '1' or [Records on CTPS] = '1')) AND (([Mobile] is NULL OR [DO NOT TELEPHONE] = 'Y') OR ([Records on TPS] = '1' or [Records on CTPS] = '1')))) and [CAT Status] = 1 then 'A-B2F-CL-DM-M2-CAT1-GL'
            when ((([Email] is NULL and [Site Email] is null) or [Do NOT EMAIL] = 'Y') and ((([Telephone] is NULL OR [DO NOT TELEPHONE] = 'Y') OR ([Records on TPS] = '1' or [Records on CTPS] = '1')) AND (([Mobile] is NULL OR [DO NOT TELEPHONE] = 'Y') OR ([Records on TPS] = '1' or [Records on CTPS] = '1')))) and [CAT Status] = 2 then 'A-B2F-CL-DM-M2-CAT2-GL' end as [CampaignCode]
    , case when ((([Email] is not NULL or [Site Email] is not null) and [Do NOT EMAIL] <> 'Y') and (([Telephone] is not null or [Mobile] is not null) and [DO NOT TELEPHONE] <> 'Y' and [Records on TPS] is NULL and [Records on CTPS] is NULL)) then 'EM/TM'
            when ((([Email] is not NULL or [Site Email] is not NULL) AND [Do NOT EMAIL] <> 'Y') and (([Telephone]  is NULL AND [Mobile] is NULL) OR [DO NOT TELEPHONE] = 'Y')) then 'EM'
            when ((([Email] is NULL and [Site Email] is null) or [Do NOT EMAIL] = 'Y') and (([Telephone] is not null or [Mobile] is not null) and [DO NOT TELEPHONE] <> 'Y' and [Records on TPS] is NULL and [Records on CTPS] is NULL)) then 'TM'
            when ((([Email] is NULL and [Site Email] is null) or [Do NOT EMAIL] = 'Y') and ((([Telephone] is NULL OR [DO NOT TELEPHONE] = 'Y') OR ([Records on TPS] = '1' or [Records on CTPS] = '1')) AND (([Mobile] is NULL OR [DO NOT TELEPHONE] = 'Y') OR ([Records on TPS] = '1' or [Records on CTPS] = '1')))) then 'DM' end as [Contact_Preference]

    , NULL                                                                                                                                  as [Product_Interest]
    , NULL                                                                                                                                  as [NewServicesForCustomer]
    , [CAT Status] +',' + [Competitor available 1] + ',' + [Competitor available 2] + ',' + [Competitor available 3] + ',' + [Competitor available 4] + ',' + [Competitor available 5] + ',' + [Competitor available 6] + ',' + [Competitor available 7] + ',' + [Competitor available 8] + ',' + [Competitor available 9] + ',' + [Last year accounts paid?] as [Description]
from ML_Pre_Test_Master
where [Commercial premise (yes/no)] <> 'Yes - Closed'

Any help you could lend would be greatly appreciated

2
sql-server
You are trying to insert a NVARCHAR field into a FLOAT. Check your SELECT, or if it is the case, convert the field to FLOAT. - galloleonardo
But the source table and the final table both only have NVARCHAR fields, I never specify FLOAT anywhere, so I don't know what to do. If I were to convert the fields, where exactly would I have to convert them? In the original tables? Or can I put a cast() function somewhere in the query? - MichaelL
You say all columns in source and destination are varchar(255) but in your case statements you are testing e.g. [CAT Status] = 0 (and 1, 2 etc) this is a numeric constant so will try to convert [CAT Status] to compare it. - SQLBadPanda
As far as I knew they were, the templates for the tables were imported in from Excel and when I right click and create table to see the field types they're all coming up as nvarchar(255) How can I amend the query so that the conversion errors stop? Sorry I only have a working knowledge of SQL and I've never had to deal with this problem before. - MichaelL
Script your table dbo.ML_Records as CREATE and post it here. With all the constraints defined on it - sepupic

2 Answers

1
votes

Looks like one of your [CAT Status] row has a letter so while casting sql throwing a error. Can you check all [CAT Status] variables?

0
votes

Look at [Cat Status]... It's being concatinated into the [Descripion] string. Based on other references to that column elsewhere in the query, it would appear to have a numeric data type. Trying to concatinate a numeric data type, using the "+" symbol makes SQL Server think it supposed to be doing math and it tries to convert everything into numerical values.