insert and convert null to 0

Question

Ive got table with null values in it. I would like to copy this table to another and replace null value from one column to for example 0. Is it possible cause my target table doesnt accept null values in this colum

Insert into MyNewTable (A, B, C) select (AA, BB, CC) from MyOldTable

And CC may be null

thanks for any help

Answer 1

Just use ISNULL()

Insert into MyNewTable (A, B, C) select (AA, BB, ISNULL(CC,0)) from MyOldTable

Answer 2

You can use both IsNull(a, b) or Coalesce(a, b, c, ...) functions, where IsNull returns the value of "b" if "a" is null, and Coalesce returns the first non-null argument. An important difference is that IsNull() forces the casting (or convert) of "b" to the type of "a", while Coalesce's return type is the same of the returned argument, letting the engine to try any conversion only AFTER the evaluation of the function.

I.e. if the column "a" is of int data type, writing IsNull(a, '1') is ok, but IsNull(a, 'hello') causes a conversion error, while writing Coalesce(a, 'hello') is allowed, and an error will be raised only if a is null and you try, for example, to insert the returned value ('hello') to an int column, but doing auotmatic conversion of a's value (if not null) when inserting in a varchar column.