Generating SQL Insert Statements From A Data Table

Every now and then one is faced with a situation where they have to write and execute insert statements with existing tables and data. Having to personally deal with this before, a friend of mine shed some light as to how to generate SQL Insert statements. This comes in handy when you may not have access to tools, such as Microsoft's Enterprise Manager for SQL server or of the sort. Below is an example of such a solution.

The following is a simple example, but it can be used as a guide to generate various queries.

select('Insert into TableName + '(' + TableColumnName1,TableColumnName2,TableColumnName3 + ')' + values(''' + TableColumnName1 + ''',''' + TableColumnName2 + ''',''' + TableColumnName3 + ''')') from TableName

The basic principle is concatenating strings. The text in italics serve as parameters, which represent your table's name and columns.

In my case, I used this to generate an insert statment for each row in a static table. The output to this looked something like the following.

Insert into TableName (TableColumnName1, TableColumnName2, TableColumnName3) values(' TableColumnName1','TableColumnName2','TableColumnName3') from TableName

As simple as this may seem, there's a few things to consider.

• After you run this script, if you have data that contains appostrophies, you will need to manually add the proper set of single quotes to the output in order to escape those characters.


• The opposite is true when dealing with Integer types. You don't need to add the single quotes within the script. Update The example as is will not work for Integers or any other type other then varchars. You will encounter an invalid casting message. I have yet to solve this one as well.


• This will not work with tables that contain nulls. The script will abend once it encounters nulls.

If anyone can post a solution to this, I would highly appreciate it.

Issue with opening VS.Net web projects.

Recently, I encountered an error message when attempting to open a web project in Visual Studio 2003. The message stated "Refreshing the project failed. Unable to retrieve folder information from the server." This is a generic message.

In my situation, this was caused by a combination of using Subversion and TortoiseSVN as my source control.

Within the help files I found the following solution to this issue.

"Use "_svn" instead of ".svn" directories
VS.NET when used with web projects can't handle the .svn folders that Subversion uses to store its internal information. This is not a bug in Subversion. The bug is in VS.NET and the frontpage extensions it uses. Read Section 5.26.7, “Subversion Working Folders” to find out more about this issue.
If you want to change the behaviour of Subversion and TortoiseSVN, you can use this checkbox to set the environment variable which controls this.
You should note that changing this option will not automatically convert existing working copies to use the new admin directory. You will have to do that yourself using a script (See our FAQ) or simply check out a fresh working copy."

The checkbox they are referring to is within the "Settings" dialog. This can be located from the Tortoise's context menu (usually accessible by right clicking on the directory of your project). You can find the checkbox within the General section.

In section 5.26.7 it goes on to say...

"5.26.7. Subversion Working Folders
VS.NET when used with web projects can't handle the .svn folders that Subversion uses to store its internal information. This is not a bug in Subversion. The bug is in VS.NET and the frontpage extensions it uses.
As of Version 1.3.0 of Subversion and TortoiseSVN, you can set the environment variable SVN_ASP_DOT_NET_HACK. If that variable is set, then Subversion will use _svn folders instead of .svn folders. You must restart your shell for that env variable to take effect. Normally that means rebooting your PC. To make this easier, you can now do this from the general settings page using a simple checkbox - refer to Section 5.26.1, “General Settings”.

One thing to keep in mind is that making this setting change is a general (global) change and will impact other existing working folders. You may find that when you try to perform an "Update", Tortoise will give you a "Skipped" message. What this means is that you will have to perform the steps of this solution for every working directory, eventhough those directories don't contain any web projects.

I haven't tried to duplicate this on VS.Net 2005 yet, but will post my results of that as soon as I do.

Resharper Holiday Special

I'm a bit excited to now own my very own personal license of Resharper. This is a great addition to the Visual Studio IDE for developers, which provides nice features to increase productivity.

Enough with the hype. The main reason for this post is to highlight the special Resharper has for this year. Its not that easy to find this page through they're site so I thought I'd share this. This is a limited offer and for just under $100 it's worth it. It currently runs for about $149.