Guild Blogs

Great code!
I have only made a little modification to avoid errors with reserved words used in field names or field names with spaces. I have added this to the field names:[FieldName]

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

This is pretty good stuff Jake. Illustrates the practical use of Agile processes.

Try www.sqlscripter.com to generate your scripts. This tool supports all commands (Insert+Update+Delete).
Its free of charge.

Thanks for the feedback Qamar. Since I run this as an administrator, I’m not particularly worried about injection. Are you referring to overruns on data fields, buffer overruns?

Mark

Nice statement. I have been using another one but it crashes on heavy data tables. The performance of the script is not good but it generates correct statements.
One more thing, did u check sql injection in ur query?
thx
Qamar

Hi,

How would you write the code to update a table.

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

exec sql

will this work ?

Portland, OR XP Users Group Meeting - Mar 8

Agile Development Process

Agile Development Process

Agile Development Process

Great post Mark!

Portrait of an Agile Development Process

Portrait of an Agile Development Process

Portrait of an Agile Development Process

Extracting Column Names for a Table in SQL as a CSV String

Extracting Column Name for a Table in SQL as a CSV String

New Team System Stuff - 2005-02-15

New Team System Stuff - 2005-02-15

Great post Jake!

Jerrad Anderson (http://www.jerradanderson.com/) left a comment on http://weblogs.asp.net/wallen/archive/2005/02/09/370080.aspx

—-

I have a nice set of scripts here that also demonstrate sql creating sql.

http://www.jerradanderson.com/index.php?sectionId=17&pageId=28 Remove Comment 370435