Error converting data type varchar to bigint: INSERT INTO SELECT

Question

I keep getting this error when trying to execute a stored procedure and am not sure why:

Msg 8114:
Error converting data type varchar to bigint.

Please see my SQL query below:

INSERT INTO StagingArea.dbo.DimStudentsTEST
       (StudentCode, Module, Year, UniqueStudentID)
    SELECT DISTINCT
        RTRIM(S.STUDENT_Student_ID) + 
            RTRIM(SUBSTRING(AY.ACADEMYR_Academic_Year_Code, 3, 2) +
            SUBSTRING(AY.ACADEMYR_Academic_Year_Code, 8, 2)) AS 'UniqueStudentID',
        ...
    FROM
        ...
    INNER JOIN
        ...

I believe the error is emerging because of the UniqueStudentID I have tried using CAST & CONVERT around the SELECT line but still no luck. Perhaps I am using in the wrong way. I have a feeling it is maybe because the column of the table I am pulling the data from ("AY.ACADEMYR..") is not of datatype 'bigint' so the error message keeps occuring. The datatype of the 'UniqueStudentID' column is of datatype 'bigint'

Can anyone see where the problem lies or if I am meant to use the CAST/CONVERT function then how best to use in this scenario.

Many thanks,

Sample data and expected results will help us help you. The error, however, can't be more clear. What about it don't you understand? — Larnu
The colums in your SELECT need to be in the correct order of the INSERT. The names are ignored. Right now you're trying to stuff UniqueStudentID into StudentCode, which is probably not right. — Jeroen Mostert
Your insert statement lists StudentCode first, yet your select appears to be returning UniqueStudentID first; Simply fix your column ordering either in your select or your insert column list. — Stu
@JeroenMostert Sorry I have made sure the INSERT and SELECT are in the same order...but its not in the same order of the actual table its inserting into (DimStudentsTEST)..does that make a difference? — Daisy
@Stu Sorry I have made sure the INSERT and SELECT are in the same order...but its not in the same order of the actual table its inserting into (DimStudentsTEST)...does that make a difference? Should have made that clear in the query above — Daisy

John Cappelletti John Cappelletti · Accepted Answer · 2021-06-23T18:32:22

Assuming the columns are in the proper order during the INSERT.

I suspect you there may be some unexpected data/strings in the underlying components.

To identify the bogus records, try the following using try_convert(). As you may know, try_convert() will return a NULL if the conversion fails.

Example

Select *
 From  ...
 Where try_convert(bigint,RTRIM(S.STUDENT_Student_ID)+RTRIM(SUBSTRING(AY.ACADEMYR_Academic_Year_Code,3,2)+SUBSTRING(AY.ACADEMYR_Academic_Year_Code,8,2))) is null

Error converting data type varchar to bigint: INSERT INTO SELECT

1 Answers