December 27, 2003 at 9:04 am
I am trying to replace all the null values in a table without having to hardcode each field name in a replace statement.
I've tried cursors, schemas,...... no luck so far
Marsana
Marsana
December 28, 2003 at 4:03 pm
This is not perfect code. If you want it to work in all cercumstances, you'd have to take into account the datatype of each column and exclude identity columns. But it should give you the general idea.
BTW, why would you want to do this? Do you really have so many columns in your table that you don't want to write them all out?
declare @SQLvarchar(8000)
declare @table varchar(255)
declare @cnvarchar(255)
select @table = 'x'
declare @columns table
(
cnvarchar(255) not null
)
insert into @columns
select a.name
from syscolumns a,
sysobjects b
where a.id = b.id
and b.name = @table
select * from @columns
select @SQL = 'update ' + @table + ' set '
DECLARE null_cursor CURSOR FOR
SELECT cn FROM @columns
OPEN null_cursor
FETCH NEXT FROM null_cursor INTO @cn
WHILE @@FETCH_STATUS = 0
BEGIN
select @SQL = @SQL + @cn + ' = isnull(' + @cn + ', ""), '
FETCH NEXT FROM null_cursor INTO @cn
End
CLOSE null_cursor
DEALLOCATE null_cursor
select @SQL = left(@SQL, len(@SQL) - 1)
exec(@SQL)
December 28, 2003 at 4:56 pm
thank you so much for your reply
Yes unfortunately some of the tables in Solomon V have upward around 30-100 fields in some cases and there is like 200 tables.
Marsana
Marsana
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply