Not able to update column which was set as null using 'select into' statement in sybase

Question

I am creating a temp table in sybase like below

select col1 = null, col2 =2 into #myTable 

Here when I try to update col1

update #myTable  set col1 = 'test'

I get error - "[Error Code: 257, SQL State: 42000] Implicit conversion from datatype 'VARCHAR' to 'INT' is not allowed. Use the CONVERT function to run this query."

Can anyone please help me fix it?

Answer 1

Assuming this is Sybase ASE (257 is a standard ASE system error number) ...

col1=null doesn't tell the database what the datatype of col1 should be so the database defaults the column's datatype to int.

When creating a table via select/into you need to insure each column is created with the desired datatype. For this particular instance try:

select col1=convert(varchar(10),null), col2=convert(tinyint,2) into #myTable 

NOTES:

• modify the convert() calls to reference the desired datatypes
• when the new column is populated from another table's column(s) the source column(s) datatypes will be used in determing the datatype of the new column

Also keep in mind the following:

select col1='test' into #otherTable

The datatype for col1 will be determined from the initial data value; in this case the value 'test' tells the database you need to store 4 characters so the database will default the column's datatype to varchar(4). This should be fine as long as you never intend to insert anything longer than varchar(4) otherwise you'll need to provide a convert() with the initial select/into to explicitly state the column's datatype, eg:

select col1=convert(varchar(35),'test') into #otherTable

---

Assuming you get past the Implicit conversion from datatype 'VARCHAR' to 'INT' is not allowed. error message your next bump-in-the road may occur if you try something like:

update #myTable set col2 = NULL

With the result being that you're presented with an error message similar to column does not allow nulls.

As with datatype determination, Sybase (ASE) will try to determine a column's NULLability in a few different ways:

• if column is assigned a 'value' of NULL (as in the example: col1 = null) then ASE will configure the column to allow NULLs
• if the column's value is being copied from another table then the source column's NULLability will be used in determining the new column's NULLability
• if the query explicitly defines the column as NULLable (see example - below) then the column will be configured to allow NULLs
• if the database option allow nulls by default is false (ASE default setting) then the column's NULLability will be set to 'not NULL'
• when all else fails ...
• if the database option allow nulls by default is true then the column's NULLability will be set to 'NULL'(able)

An example of explicitly defining the column to allow NULLs:

select col1 = convert(varchar(35) null,'test') into #otherTable