This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing a T-SQL Script for updating all NULL record columns by Zero in the SQL Server.
Recently, I executed this script on few measurement related tables where I updated all NULL data with Zero. Most of those tables, I am using for our operational mathematical operation so I put ZERO.
Check the below script:
Create a table with sample data:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE TABLE tbl_TestNulls ( Code1 INT ,Code2 INT ,Code3 INT ) GO INSERT INTO tbl_TestNulls VALUES (1,NULL,NULL),(NULL,2,NULL),(3,NULL,4) ,(NULL,NULL,NULL),(5,6,NULL),(NULL,NULL,7) GO |
Solution:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
declare @tableName varchar(50) declare @sql nvarchar(max) set @sql = '' set @tableName = 'tbl_TestNulls' SELECT @sql = @sql + 'update ' + @tableName + ' set ' + QUOTENAME(t.[name]) + '=0 where ' + QUOTENAME(t.[name]) + ' is null; ' from (SELECT [name] FROM syscolumns where id = (select id from sysobjects where type = 'U' and [NAME] = @tableName)) as t execute sp_executesql @sql |
Result:
|
1 2 3 4 5 6 7 8 9 10 |
select *from tbl_TestNulls Code1 Code2 Code3 ----------- ----------- ----------- 1 0 0 0 2 0 3 0 4 0 0 0 5 6 0 0 0 7 |
Leave a Reply