SQL Server – Force INSERT statement to use varchar instead of int [closed]

Posted on

Question :

I’m trying to make a large insert to a new table in SQL Server that’s set up with a particular column of type varchar(56). Most of the values for that column in the insert rows are integer values, but placed inside quotes (i.e. ‘6’). There are, of course, a number of non-numeric values in there as well.

I understand that SQL server will implicitly convert a varchar column to int if it sees a lot of values that can be converted (http://www.sql-server-helper.com/error-messages/msg-245.aspx is where I got that from. Only thing I’ve found online so far that at least seems to hit close to the problem I’m encountering.)

Is there any way to force it to not do that? I’d really rather not have to sort through all these rows to insert the text-valued rows first and then the numeric-valued rows.

Example

insert into [MyDB].[dbo].[Questions] (
     MAP_SRC
    ,Q_ID    
    ,R_ID    
    ,R_TXT   
    ,TEXT_1  
    ,TEXT_2  
    ,EXT_ID      
    ,EXT_Q_ID    
    ,EXT_VALUE   
    ,EXT_VALUE_2
    ,EXT_VALUE_3
)    
values
     ('D','1','1','',null, null,'1','N32','date txt','Date','')
    ,('D','2','2','',null, null,'2','N33','2','','')

…and so on. It’s the EXT_VALUE column which is causing the issues. I’ve tried inserting the text-valued rows first to try to establish that the column is supposed to be varchar and not int, and putting one such row at the beginning of the inserts, but it’s all for naught so far.

Answer :

A simple way is cast it to VARCHAR(56)

Ex:

INSERT INTO MY_TABLE (Field1, Field2, Field3)
SELECT Field11, Field12, CAST(Field13 AS VARCHAR(56))
FROM   ANOTHER_TABLE;

I’d suggest you to check for unquoted values in your insert sentences.

Leave a Reply

Your email address will not be published. Required fields are marked *