CODEWITHSUNDEEP
sqlsql-server-2005
gruber
gruber

Reputation: 29727

insert and convert null to 0

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

Upvotes: 4

Views: 12619

Answers (3)

BertuPG
BertuPG

Reputation: 653

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.

Upvotes: 5

Daan
Daan

Reputation: 6994

You can use the coalesce function for this:

INSERT INTO MyNewTable (A, B, C)
SELECT COALESCE(AA, 0), COALESCE(BB, 0), COALESCE(CC, 0) FROM MyOldTable

Upvotes: 1

Sachin Shanbhag
Sachin Shanbhag

Reputation: 55489

Just use ISNULL()

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

Upvotes: 6

Related Questions