This is your code with my modifications:

create table #tmp (
SQLText varchar(8000) )

create table #tmp2 (
Id int identity,
SQLText varchar(8000) )

set nocount on

delete #tmp
delete #tmp2

declare @vsSQL varchar(8000),
@vsCols varchar(8000),
@vsTableName varchar(40)

declare csrTables cursor for
select name
from sysobjects
where type in (’u')
and name like ‘AMEX%’
order by name

open csrTables
fetch next from csrTables into @vsTableName

while (@@fetch_status = 0)
begin

select @vsSQL = ‘’,
@vsCols = ‘’
select @vsSQL = @vsSQL +
CASE when sc.type in (39,47,61,111) then
‘”””'’+’ + ‘isnull(rtrim(replace([’+ sc.name + ‘],”””'’,”””””'’)),”'’)’ + ‘+””'’,'’+’
when sc.type = 35 then
‘”””'’+’ + ‘isnull(rtrim(replace(substring([’+ sc.name + ‘],1,1000),”””'’,”””””'’)),”'’)’ + ‘+””'’,'’+’
else
‘isnull(convert(varchar,[’ + sc.name + ‘]),'’null'’)+'’,'’+’
end
from syscolumns sc
where sc.id = object_id(@vsTableName)
order by ColID

select @vsCols = @vsCols + quotename(sc.name,’[') + ‘,’
from syscolumns sc
where sc.id = object_id(@vsTableName)
order by ColID

select @vsSQL = substring(@vsSQL,1,datalength(@vsSQL)-1)

select @vsCols = substring(@vsCols,1,datalength(@vsCols)-1)

insert #tmp
exec (’select ‘ + @vsSQL + ‘ from ‘ + @vsTableName)

update #tmp
set sqltext = ‘insert ‘ + @vsTableName + ‘(’ + @vsCols + ‘) values(’ + substring(sqltext,1,datalength(sqltext)-1) + ‘)’

insert #tmp2
select ‘DELETE from ‘ + @vsTableName

insert #tmp2 values (’GO’)

if (select count(id) from syscolumns where id = object_id(@vsTableName) and ((status & 128) = 128) ) = 1
begin
insert #tmp2
select ’set identity_insert ‘ + @vsTableName + ‘ on’
end

insert #tmp2
select * from #tmp

if (select count(id) from syscolumns where id = object_id(@vsTableName) and ((status & 128) = 128) ) = 1
begin
insert #tmp2
select ’set identity_insert ‘ + @vsTableName + ‘ off’
end

insert #tmp2 values (’GO’)

insert #tmp2
select ‘update statistics ‘ + @vsTableName

insert #tmp2 values (’GO’)

delete #tmp

fetch next from csrTables into @vsTableName

end

close csrTables
deallocate csrTables

update #tmp2
set sqltext = substring(sqltext,1,charindex(’,)’,sqltext)-1) + ‘,NULL)’
where not(charindex(’,)’,sqltext) = 0)

update #tmp2
set sqltext = replace(sqltext, ‘,””’,',null’)
where not (charindex(’,””’,sqltext) = 0)

update #tmp2
set sqltext = replace(sqltext, ‘(””’,',null’)
where not (charindex(’(””’,sqltext) = 0)

set nocount off

select sqltext from #tmp2 order by id

go

drop table #tmp
drop table #tmp2

Mark

How would you write the code to update a table.

sql = ‘update #table set’+@col+’= ‘+@var+’where colname =’+@val

exec sql

will this work ?