update tb set col1=case when col1 is null then 0 else col1 end, col2=case when col2 is null then 0 else col2 end, ...
INSERT INTO tb SELECT NULL,NULL select * from tb ----------- id city 1 a 1 b 1 c 2 x 2 y 2 z 0 0 0 0 NULL NULL NULL NULL declare @s1 varchar(1000),@s2 varchar(1000),@i as int select @i=COUNT(name) from syscolumns where id=OBJECT_ID('tb') while @i>0 begin set @s1='' set @s2='' select @s1=name+' is null ' from syscolumns where id=OBJECT_ID('tb') and colid=@i select @s2=name+'=''0''' from syscolumns where id=OBJECT_ID('tb') and colid=@i exec ('update tb set '+@s2+' where '+@s1+'') set @i=@i-1 endselect * from tb -------------- id city 1 a 1 b 1 c 2 x 2 y 2 z 0 0 0 0 0 0 0 0楼主测一下
以下方法,要求保证所有的列均可更新为0: DECLARE @table_name VARCHAR(100) SET @table_name = 'table_name'DECLARE @sql VARCHAR(8000) SET @sql = ''SELECT @sql = @sql + '[' + [Name] + '] = CASE WHEN ['+ [Name] +'] IS NULL THEN 0 ELSE ['+ [Name] +'] END,' FROM SysColumns WHERE id = OBJECT_ID(@table_name)IF @sql <> '' BEGIN SET @sql = 'UPDATE ['+ @table_name +'] SET '+ SUBSTRING(@sql, 1, LEN(@sql)-1) EXEC (@sql) END
如果只是一列中的空值赋0,能不能一句话就行了?我是在别的程序中调用的。 Dim sSQL3 As String = String.Format("Update {0} set {1}={2} where {3}={4}", "天津保税区管线", "共爆管次数", "0", "共爆管次数", "''") workspace.ExecuteSQL(sSQL3)
set
col1=case when col1 is null then 0 else col1 end,
col2=case when col2 is null then 0 else col2 end,
...
SELECT NULL,NULL
select * from tb
-----------
id city
1 a
1 b
1 c
2 x
2 y
2 z
0 0
0 0
NULL NULL
NULL NULL
declare @s1 varchar(1000),@s2 varchar(1000),@i as int
select @i=COUNT(name) from syscolumns where id=OBJECT_ID('tb')
while @i>0
begin
set @s1=''
set @s2=''
select @s1=name+' is null ' from syscolumns where id=OBJECT_ID('tb') and colid=@i
select @s2=name+'=''0''' from syscolumns where id=OBJECT_ID('tb') and colid=@i
exec ('update tb set '+@s2+' where '+@s1+'')
set @i=@i-1
endselect * from tb
--------------
id city
1 a
1 b
1 c
2 x
2 y
2 z
0 0
0 0
0 0
0 0楼主测一下
DECLARE @table_name VARCHAR(100)
SET @table_name = 'table_name'DECLARE @sql VARCHAR(8000)
SET @sql = ''SELECT
@sql = @sql + '[' + [Name] + '] = CASE WHEN ['+ [Name] +'] IS NULL THEN 0 ELSE ['+ [Name] +'] END,'
FROM SysColumns
WHERE id = OBJECT_ID(@table_name)IF @sql <> ''
BEGIN
SET @sql = 'UPDATE ['+ @table_name +'] SET '+ SUBSTRING(@sql, 1, LEN(@sql)-1)
EXEC (@sql)
END
Dim sSQL3 As String = String.Format("Update {0} set {1}={2} where {3}={4}", "天津保税区管线", "共爆管次数", "0", "共爆管次数", "''")
workspace.ExecuteSQL(sSQL3)
set col=0
where col is null