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.
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.
A simple way is cast it to
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.