tsql - SQL Server 2005: Insert query returning Operand type clash: ntext is incompatible with smallint -

i have hope quick question. haven't found working code this, , i'm @ little bit of loss.

i'm working old database on sql server 2005. has dynamic questionnaire (or thousand, actually... long story), , each question can have answer stored either smallint or ntext.

the current "answers" table follows:

answerid (pk, int, not null) evalid (int, null) questionid (int, null) numericanswer (smallint, null) textanswer (ntext, null) 

(yes, know ntext deprecated. it's old table.)

this table has on 300,000 records in it, , getting large. i'm looking more efficient ways store data, , decided little experiment moving single sql_variant field (currently, each row can have data in 1 of 2 answer columns, not both).

so made table, called answerstest:

answerid (pk, int, not null) evalid (int, null) questionid (int, null) answergiven (sql_variant, null) 

... , i'm trying of existing data answers answerstest, preferably in original order... i'm running insert query, , it's not working. i've tried both following:

insert answerstest (evalid, questionid, answergiven) select evalid, questionid, isnull(numericanswer,textanswer) answergiven answers order answerid 

...and

insert answerstest (evalid, questionid, answergiven) select evalid, questionid, convert(ntext,case when numericanswer null textanswer else numericanswer end) answergiven answers order answerid 

in both cases, following: operand type clash: ntext incompatible smallint

i know there must simple answer this... i'm drawing blank (it's been couple of months since i've had tsql), , can't find working answer through searching. please help. :)

edit: found answer through trial , error...

the answer may not have been best 1 (and may jump through unnecessary hoops), it's one-time query , works. :)

insert answerstest (evalid, questionid, answergiven) select evalid, questionid, case when convert(sql_variant,numericanswer) null convert(sql_variant,convert(varchar(8000),textanswer)) else convert(sql_variant,numericanswer) end answergiven answers order answerid 

(note: tried varchar(max), wasn't allowed. answers weren't long, though... nobody writes essays in these surveys... changed varchar(8000), , worked).

thanks reading, , sorry take time! :)

faced similar error when accidentally gave string values column defined int. please rearrange values , table fields in insert statement ex

insert table (int,string) values (string,int) 

will not work , generate error stated above. re arrange in respective manner.

 insert table (int,string) values (int,string)