Latest Comments

In response to: Dynamic SQL INSERT Generator Unleashed!

Comment from: Alfredo
Alfredo

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

05/23/06 @ 05:30

In response to: Portrait Of An Agile Development Process

Comment from: jet pizarro
jet pizarro

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

05/11/06 @ 15:57

In response to: Dynamic T-SQL in Just Minutes!

Scott

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

01/24/06 @ 11:30

In response to: Dynamic SQL INSERT Generator Unleashed!

Comment from:
Mark

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

01/19/06 @ 07:53

In response to: Dynamic SQL INSERT Generator Unleashed!

Comment from: Qamar hafeezi
Qamar hafeezi

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

01/19/06 @ 05:56

In response to: Dynamic T-SQL in Just Minutes!

Comment from: andrew krenitz
andrew krenitz

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 ?

08/23/05 @ 22:00

In response to: Portrait Of An Agile Development Process

Wayne Allen's Weblog

Portland, OR XP Users Group Meeting - Mar 8

02/28/05 @ 08:12

In response to: Portrait Of An Agile Development Process

Tate's Blog

Agile Development Process

02/21/05 @ 19:27

In response to: Portrait Of An Agile Development Process

Tate's Blog

Agile Development Process

02/21/05 @ 19:27

In response to: Portrait Of An Agile Development Process

Tate's Blog

Agile Development Process

02/21/05 @ 19:13

In response to: Excellent Consultants...or Why Technology Kingdoms Rise and Fall

Comment from:
wayne

Great post Mark!

02/21/05 @ 10:49

In response to: Portrait Of An Agile Development Process

Dot Net Monkey

Portrait of an Agile Development Process

02/20/05 @ 17:17

In response to: Portrait Of An Agile Development Process

Dot Net Monkey

Portrait of an Agile Development Process

02/20/05 @ 17:17

In response to: Portrait Of An Agile Development Process

Dot Net Monkey

Portrait of an Agile Development Process

02/20/05 @ 17:17

In response to: Dynamic SQL INSERT Generator Unleashed!

Wayne Allen's Weblog

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

02/18/05 @ 09:25

In response to: Dynamic SQL INSERT Generator Unleashed!

Wayne Allen's Weblog

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

02/18/05 @ 08:41

In response to: Portrait Of An Agile Development Process

Rob Caron's Blog

New Team System Stuff - 2005-02-15

02/15/05 @ 13:41

In response to: Portrait Of An Agile Development Process

Rob Caron's Blog

New Team System Stuff - 2005-02-15

02/15/05 @ 13:39

In response to: Portrait Of An Agile Development Process

Comment from:
wayne

Great post Jake!

02/14/05 @ 12:11

In response to: Dynamic SQL INSERT Generator Unleashed!

Comment from:
iclemartin

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

02/10/05 @ 11:44
July 2022
Mon Tue Wed Thu Fri Sat Sun
        1 2 3
4 5 6 7 8 9 10
11 12 13 14 15 16 17
18 19 20 21 22 23 24
25 26 27 28 29 30 31
 << <   > >>

Search

  XML Feeds

Real Time Web Analytics
powered by b2evolution CMS