Guild Blogs

Okay, so I'm hyping a bit. So give me a break...this little gem took me a few days to write and weeks to refine in the 90's. Anyway, I updated it to take advantage of the "dynamic aggregator" approach shown in my previous query.

So you are probably saying, "Great, so you can generate a CREATE TABLE statement...Whoopee!". Well, if I could help you fill that table or tables using a script that dynamically generates INSERT statements based on the contents of a table, would that be worth something to you? Great! Read further and lets really impress those SQL jockey friends of yours.

Problem to Solve
Write a query that dynamically generates a series of INSRET statements based on the contents of a table. The output of this query can then be used to fill an empty table or replace a table. It also accomodates preserving IDENTITY columns.

Goals
Like always, I try to make my SQL tools reusable and tight. In this query, I opt to blend simplicity (one cursor for the tables) with the powerful "dynamic aggregator" shown in my last blog (for the columns). So without further delay....

Solution to the Problem
Paste the following query into a SQL Server Query Analyzer window and set the current database to the desired database that you want to generate the INSERT script for. Change the WHERE clause of the DECLARE CURSOR query to reflect one or more tables from that database. Set the output format to Text. Then let 'er fly! (eg: run the query)....


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 in ('Customers')
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 + 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


The resulting output will be an auto-generated INSERT script that can be used to migrate data and/or seed a new database/tables.

Things to grok:

• HONEST DISCLAIMER: Notice that this has limitations (8000 characters of SQL per INSERT line, NO image data types). It handles about 90-95% of the needs that I use it for, but please understand that I have tweaked and embellished it over time. So use it at your own risk. If you find a bug/enhancement, please let me know.
• Notice the nifty "dynamic aggregator" queries used to build up the main @vsSQL and @vsCols variables. This pattern can be leveraged over and over to generate powerful SQL
• Don't freak about the batch of single quotes. If you are going to do dynamic SQL, you need to know how to use these. Each quote must be represented by two single quotes. For example: SELECT '''' will result in the display of one (1) single quote in the result set. The best thing to do is to experiment with this until you grok it.
• Notice the use of the "EXEC (@sql)" construct to dynamically execute SQL to generate more SQL.
• Notice the concluding few UPDATE queries to "massage" the INSERT SQL to handle NULLs and final columns in the table. Why punish yourself by embedding all of the conditional logic in the main queries when a few simple UPDATE statements can be used to "massage" the data at the end. I use this pattern routinely. This is one distinct advantage of SQL/Set based operations vs. iterative constructs...So use it!

Coming next on my blog:
Hope you found this blog entry useful. I know that this tool has been indispensable for me. So, you may be wondering..."Is this guy just a SQL junkie?" Well, the answer is yes and no. I am a SQL Junky, but not "just" a SQL junky. I also specialize in the design, architecture and implementation of enterprise-wide n-tier applications.

So, I'm going to share a bit of a blend in my next blog. See how I leverage the pervious pattern to generate VB.NET Business Classes to be consumed in an n-tier .NET Smart Client application..."Dynamic SQL begets .NET Business Classes". My theory is, I like classes and I like SQL, so why code redundant patterns by hand? The answer is, you don't have to!

Now, go and "REALLY" impress your SQL jockey friends!

You record the Superbowl on the PVR so you can watch the commercials, but
your wife makes you watch some of the plays...

With the Open Source community back in the spotlight I'm truly perplexed by what motivates them http://www.technewsworld.com/story/40315.html, and I have mixed emotions about the effect they've had on the software industry.

I respect someone who puts great effort into a software application and gives it away to the world free of charge. There are many amazing tools and programs available to us all. I've found myself relying on Open Source tools during past projects, and find them to be almost uniformly of high quality. Think of Apache for web servers, NAnt for build process management, or similar high quality applications.

However, my patience starts running thin when Open Source advocates turn from their passion for great software, and the spirit of sharing, to preaching against the evils of the profit motive. It's one thing to actively promote the benefits of free software http://gnu.netvisao.pt/philosophy/why-free.html, it's an entirely different proposition to alienate and berate those who build and/or use proprietary software.

In my opinion, Open Source developers fall into three categories:

True Believers
This group lives and breathes Open Source, fully grasps the philosophy behind it, and despises most things corporate, and all things Microsoft. True Believers don't differentiate Open Source from the general realm of the greater world of politics. In their view, Open Source is just the latest tool in the on-going struggle against the evils of capitalism, corporations, and corruption. Richard Stallman http://www.stallman.org/ personifies this group.

Profit Seekers
This group is far less vocal than True Believers but see an opportunity to make a buck creating products and services around the Open Source concept. To an extent, they speak the language of the True Believers, but observing their actions, clearly are building revenue streams to make money. Profit Seekers struggle with deep-seated conflictions due their profit seeking motive banging up against the free and sharing philosophy of Open Source licensing agreements http://www.gnu.org/licenses/licenses.html#GPL.
Mathew Szulick http://www.redhat.com/about/corporate/team/szulik.html personifies this group.

Dabblers
This wide-ranging group cuts across many different and unassociated sub-groups. From students checking out new technologies, to workers pushed toward Open Source because it's used at their place of work, to the technically adept who could care less about the philosophy of 1s and 0s, and simply want to use the best available solution, Dabblers are either unaware of the battle Open Source True Believers are in, or who are aware, but are practical in their ways and care far more about putting food on the table than psycho-babble about "technical purity".

Finally, let me conclude with this.. in my view there is room for both Open Source products and services, as well as people like myself trying to earn an honest buck by "selling out". The war some in the Open Source movement have ignited only serves to bring the entire industry down... with outsourcing, razor competition, and the inherent complexity of technology, that last thing we need is a flame war between developers that has no practical purpose. In a world where software doesn't pay, where's the motivation to get up in the morning and slog away for ten hours cutting code? It may seem well intended to share your hard work with the world free of charge, but as the old saying goes, the road to hell is paved with good intentions.

I've come to rely on this great little utility from Steve Miller.

Have you ever copied some text from a web page, a word document, help, etc., and wanted to paste it as simple text into another application without getting all the formatting from the original source? PureText makes this simple. Just copy/cut whatever you want to the clipboard, click on the PureText tray icon, and then paste to any application.  Better yet, you can configure a Hot-Key to convert and paste the text for you.  The pasted text will be pure and free from all formatting.

Scott Came will be serving as chair of this committee charged with coordinating the development of reference exchange documents initiated by prosecutors.

Ever feel left behind when one of those SQL Server jockies breezes through a complex query that generates SQL on the fly? After all its a bit over the top to use SQL to generate SQL, isn't it? Well, its time to level the playing field. I'm going to pass on a simple pattern that you can use to generate the most sophisticated queries. This pattern will lead to an endless supply of SQL/Database tools to add to your library.

Problem to Solve
Write a query that generates a CREATE TABLE definition from any table. Then write a query that generates INSERT statements to fill a copy of that table based on the current data.

So you are probably saying to yourself...Wouldn't just be easier to type the CREATE TABLE definition by hand? Maybe so for the first one, but imagine doing it for an entire database of 300+ tables. Or you may say...Couldn't I just use the T-SQL generator in Enterprise Manager (or equivalent)? Sure, but if you want to take your skills to the next level, its time to make an investment. Besides, does anyone else hate the excess baggage that those tools include in the generated SQL? Me too. So, dynamic SQL to the rescue.

Goals
I always try to make my SQL tools reusable and tight. I could do a lot of things using brute force CURSORS or WHILE loops in SQL, but I try to avoid that. So I'm going to give you a little gem that will aggregate the statement on-the-fly in one SQL statement. So without further delay....

Solution to the Problem
Paste the following query into a SQL Server Query Analyzer window and set the current database to Northwind. Set the output format to Text. Then run the query....


declare @vsSQL varchar(8000)
declare @vsTableName varchar(50)
select @vsTableName = 'Customers'

select @vsSQL = 'CREATE TABLE ' + @vsTableName + char(10) + '(' + char(10)

select @vsSQL = @vsSQL + ' ' + sc.Name + ' ' +
st.Name +
case when st.Name in ('varchar','varchar','char','nchar') then '(' + cast(sc.Length as varchar) + ') ' else ' ' end +
case when sc.IsNullable = 1 then 'NULL' else 'NOT NULL' end + ',' + char(10)
from sysobjects so
join syscolumns sc on sc.id = so.id
join systypes st on st.xusertype = sc.xusertype
where so.name = @vsTableName
order by
sc.ColID

select substring(@vsSQL,1,len(@vsSQL) - 2) + char(10) + ')'


The resulting output will be a auto-generated CREATE TABLE statement:

CREATE TABLE Customers
(
CustomerID nchar(10) NOT NULL,
CompanyName nvarchar(80) NOT NULL,
ContactName nvarchar(60) NULL,
ContactTitle nvarchar(60) NULL,
Address nvarchar(120) NULL,
City nvarchar(30) NULL,
Region nvarchar(30) NULL,
PostalCode nvarchar(20) NULL,
Country nvarchar(30) NULL,
Phone nvarchar(48) NULL,
Fax nvarchar(48) NULL
)

Things to grok:

• Notice the nifty main SQL statement that aggregates the SQL string variable @vsSQL on-the-fly.
• Notice how to leverage the combination of sysobjects (for tables), (for column Des) and systypes (for data types properties).
• Notice the use of char(10) to append carriage returns to the SQL for decent formatting.
• Notice the use of the CASE statement to conditionally append the length. This could be used to handled scale/precision for FLOAT, REAL and DECIMAL data types.

Coming next on my blog:
See my next Blog post on how to "Dynamically generate INSERT statements" from the contents of a table. This is like gold when creating database migration scripts and especially for transferring data between varying SQL based platforms (SQL Server, Sybase, Oracle, Informix, etc.).

Now, go and impress your SQL jockey friends!

A common dilemma presented to decision makers in the technology groups of many companies is whether to buy software off-the-shelf, or to build the software from scratch. As someone who has faced this decision, and faced the results from both sides, allow me to present my 2 cents.

There are certainly many situations when buying software makes sense. Buying pre-built software usually makes sense when the application has reached a level of maturity where change is minimal, such as standard accounting applications, database technologies, browsers, and the like. The age-old idiom, "why re-invent the wheel?" fits well here.

However, I would argue that some applications that seem to fit the buy criteria, such as contact management tools, often times have persuasive arguments pointing towards the opposite conclusion. For that matter, I would strongly argue that any application that must fit the unique requirements of a given organization, buying a solution from some vendor is a major blunder, and I've personally seen management careers take a major hit as the disastrous results of this ill-fated decision are eventually realized.

Here's my top reason why this is so:

Who's Process??
Why should a company or organization be forced to alter the way they are used to doing business in order to fit some new software into the process? Shouldn't it be the other way around? We see this all the time. The company has a standardized process.. albeit, typically, a flawed one. The employees are comfortable, and the work gets done. Then, some slick-tongued software salespeson comes along and convinces management that, if they would only buy their software, all your problems would magically go away. The software is purchased... often times, at an extremely high price. A few hundred thousand (or more) dollars are than spent on high-priced consultants to come in and integrate the software. Finally, a new application is launched that sort of works as advertised, as long as the company follows THEIR WAY of doing business. Employees are left scratching their heads and quietly berating the new software, and wishing they could have their old Excel spreadsheet back.

Who owns the kingdom??
Software purchased from a vendor does not usually come with the source code, and thus, getting "under the hood" is virtually impossible. This is always a shock to the decision maker not adept at technology. The software is launched, and tweaking it to fit a new model is very difficult or impossible to do. As the owner of the keys to the source code kingdom, the organization that chose to build their own version of the software can quickly react to changing requirements and control the change management process. The key word here is CONTROL.

How long is that line??
During the sales phase of the process the slick-tongued salesperson is quick to promise and always short to deliver when it comes to support. During the sale, your company is the center of the salesperson's universe... no one matters more to a good salesperson. Than, two years later, some issue comes along, and you quickly find out that the vendor's universe contains players far larger, and far more important to their bottom line than you could possibly offer. Thus, you find your problems sitting in their support queue along with everyone else. As the owner of the software, YOU control the support process. YOU decide the priorities. YOU determine how much you will spend on support. As a decision maker in a company, those are assets that make YOU look good.

Let's figure this out
My final reason why software should almost always be developed in-house is the most vital one to the success of the project. In a word... collaboration. When software is developed by your people, you can quickly and very early in the process involve the end-user community themselves, who will ultimately decide whether the software works FOR THEM. Thus, instead of layering another "new gadget" for them to fit into their already busy days, you've instead given them a personal stake in how the software will improve their jobs. This is no small thing, and I've found it makes ALL THE DIFFERENCE whether a project is ultimately successful or not.

Want Firefox to run even faster? If you've got broadband change the following settings in about:config

• network.http.pipelining = true
• network.http.pipelining.maxrequests = 30
• network.http.proxy.pipelining = true

Enjoy!

Call for Participation - Agile 2005
July 24-29, 2005. Marriott Hotel, Denver, Colorado, USA.
http://www.agile2005.org

March 1:  Submissions due for Tutorials and Workshops

March 15:  Submissions due for Research Papers, Experience Reports, and
Educators' Symposium

Agile 2005 integrates the best features of the Agile Development Conference
and XP Agile Universe to create an exciting conference about techniques and
technologies, attitudes and policies, research and experience, and the
management and development sides of agile software development. The agile
approach focuses on delivering business value early in the project lifetime and
being able to incorporate late-breaking requirements changes.  It
accentuates the use of rich, informal communication channels and frequent
delivery of running, tested systems, while attending to the human component of
software development.

Agile 2005 gives attendees access to the latest thinking in this domain, and
bridges communities that rarely get a proper chance to exchange ideas and
thoughts. It brings together researchers from labs and academia with executives,
managers, and developers in the trenches of software development. Agile 2005 is
not about a single methodology or approach, but rather provides a forum for the
exchange of information regarding all agile development technologies.

We invite submissions for the following:

• Research Papers
• Experience Reports
• Tutorials
• Workshops / Peer to Peer Sessions
• Educators' Symposium

Other conference activities include:

• Introduction to Agile (for Agile "newbies")
• Executive Summit
• Open Space

FOR MORE INFORMATION:  http://www.agile2005.org