October 19, 2004 at 8:02 am
Ok guys, here's my problem.
I have a table with ~55 fields in it and ~2000+ records. Some of the columns have no data in them but when I iterate through the database in VB, I get NULL errors.
What I need to do is go through the entire table replacing all empty fields with a <CTRL-0> = "<NULL>"
Any suggestions?
October 19, 2004 at 8:31 am
What does "empty" mean? It sounds as though your columns are already NULL. Replacing that with another NULL probably won't help you too much.
Have you looked into using COALESCE for your queries?
SELECT COALESCE(YourColumn, '') AS YourColumn
FROM YourTable
This will return '' (empty string) if YourColumn is NULL.
--
Adam Machanic
whoisactive
October 19, 2004 at 8:53 am
What I mean is that if I view the entire table som of the fields say <NULL> whilst others are just empty (nothing in them at all).
It's the ones with nothing in them that's causing me a problem.
October 19, 2004 at 9:25 am
Are you certain? You said you were getting NULL errors...
Anyway:
UPDATE YourTable
SET YourColumn = NULL
WHERE YourColumn = ''
--
Adam Machanic
whoisactive
October 19, 2004 at 9:52 am
I knew about this method but I wanted a way to do the whole table in one go.
This UPDATE method requires me to go through every single column (56 of them).
October 19, 2004 at 11:17 pm
---QUICK AND DIRTY
DECLARE @tablename varchar(30)
DECLARE @colname varchar(30)
DECLARE @updateSQL nvarchar(1000)
SET @tablename = 'dbo.tblTest' --Table in question.
DECLARE crs cursor LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT name
FROM syscolumns
WHERE (id = OBJECT_ID(@tablename))
AND type != 56 --int. Exclude other col types if required.
OPEN crs
FETCH NEXT FROM crs INTO @colname
WHILE @@FETCH_STATUS = 0
BEGIN
--print 'name=' + @colname
SET @updateSQL = 'UPDATE ' + @tablename + ' SET ' + @colname + ' = NULL WHERE ' + @colname + ' = '''''
--print @updateSQL
EXEC sp_executesql @updateSQL
FETCH NEXT FROM crs INTO @colname
END
CLOSE crs
DEALLOCATE crs
October 20, 2004 at 7:19 am
Just to give another possible situation :
Declare @TableName as varchar(256)
set @TableName = 'ObjSQL'
Select 'UPDATE ' + @TableName + ' SET ' + name + ' = NULL WHERE ' + name + ' = ''''' from dbo.syscolumns where id = object_id(@TableName) and IsNullable = 1 order by Colid
then paste the result of this query in query analyser and run... I found this to be an extremeley usefull technic over time to save time 🙂
October 20, 2004 at 4:47 pm
use pubs
declare @sql varchar(8000),
@TableName sysname
select @TableName = 'authors'
select @SQL = isnull(@SQL + char(9) + sc.Name + ' = isnull(' + sc.Name + ', ''<NULL>'')', '') + char(10)
from sysobjects so (nolock)
JOIN syscolumns sc (nolock) on so.id = sc.id
JOIN systypes st (nolock) on st.xtype = sc.xtype
Where so.name = @TableName and st.Name like '%char%'
and sc.isNullable = 1
order by sc.name
if @sql is not null and len(@SQL) <> 1
BEGIN
select @SQL = 'Update ' + @TableName + char(10) + 'set ' + @sql
print @SQL
END
Else
BEGIN
print 'There are no "char" type columns that can be null'
END
Signature is NULL
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply