CODEWITHSUNDEEP
sqlsql-servert-sql
Sam
Sam

Reputation: 103

set the null value column to 0

I am creating a temp table from the actual table. Now in the actual table for the column Rewrite_Count its showing nulls. I need those nulls to be replaced with 0. Can someone help me on getting the correct code.

SELECT *
INTO policy.Fact_Monthly_Policy_Snap_20190403
FROM Policy.Fact_Monthly_Policy_Snap;

Upvotes: 0

Views: 2304

Answers (5)

Amit Rajput
Amit Rajput

Reputation: 11

Note Select into statement copies only some properties like column name,data type but most important it doest not include constraint,trigger etc.

You can use coalesce and isnull for replacing with 0. Coalesce is standard and isnull is tsql specific. If source column is of not null then both coalesce and isnull will set the property of resulted column to not null . But if source column is null then isnull will set the resulted column to not null.

Upvotes: 1

Brian
Brian

Reputation: 1248

Add a Default to the Rewrite_Count field on Monthly_Policy_Snap. That way, if someone adds a record to the table and doesn't specify a value for Monthly_Policy_Snap, the system will set it to 0.

You can add that with Alter Table ... Alter Column

Read more about the Default constraint here.

Upvotes: 1

Ilyes
Ilyes

Reputation: 14928

Use ISNULL()

SELECT ISNULL(Rewrite_Count, 0) Rewrite_Count, --Other columns
INTO policy.Fact_Monthly_Policy_Snap_20190403
FROM Policy.Fact_Monthly_Policy_Snap;

Upvotes: 1

Anusha Subashini
Anusha Subashini

Reputation: 397

Try this,

update policy.Fact_Monthly_Policy_Snap_20190403
set Rewrite_Count  = IsNull(Rewrite_Count,0)
where Rewrite_Count is null

Upvotes: 1

Eray Balkanli
Eray Balkanli

Reputation: 7960

You can basically update the value of the column:

update policy.Fact_Monthly_Policy_Snap_20190403
set Rewrite_Count  = 0
where Rewrite_Count is null

The solution above assumes Rewrite_Count column is type of integer.

Upvotes: 2

Related Questions