Mark Clerget's Blog

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

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 ?

Great post Mark!

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

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

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

Can you list some of the sample output please?

Dynamic SQL INSERT Generator Unleashed!

Great Job again!

I only had to make a minor change on one of my tables. I had to add [ ] around the field name, because it was named Key.

Thanks again!

Thanks David and Gregor for the positive feedback.

The purpose of this post was to give primer on the system tables as well as Dynamic SQL for novice to intermediate users. The next post should provide a solid tool for advanced users as well.

–Mark

Great article!

Really looking forward to the next one though! As the previous commentor mentioned, the CREATE TABLE scripts are reasonably easy to come by. But a full script for INSERT is definitely going to be interesting to see!

Thanks again!

If you press ctrl-c on a table or stored proc and then do ctrl-v into notepad or any other program such as query anaylser youll get the full create sql as well.

Cheers
Gregor

Writing SQL with SQL