Generate SQL Server Inserts from existing data – with Identity Inserts

A good while ago I posted a stored proc that would generate insert statements for table data, along with simple filtering capability.

I broke this out again today, as I needed to recreate part of a database on a local machine.  I didn’t have knowledge of the schema so I just went about fixing each constraint error and adding the reference tables as required to my script.  After manually adding ‘SET IDENTITY_INSERT xx’ about a dozen times I added the functionality to the Stored Proc – so if your table has an identity column it will now wrap the results in IDENTITY_INSERT statements, saving you a bit more time, and headaches.

An interesing side effect if you’re explicitly inserting identity values is that the order of your inserts may become pertinent – especially if you’ve got foreign keys referencing the same table.  I then just added the capability to ‘order by’.
There’s no automatic way to switch off column headers, so you’ll need to configure that in the Resutls Output options in Query Analyzer (or management studio).

If you run the proc into ‘Northwind’ and run:

usp_insertgenerator Employees, @order=’EmployeeID’

You’ll get…


INSERT Employees(EmployeeID,LastName,FirstName,Title,TitleOfCourtesy,BirthDate,HireDate,
  VALUES(‘1′,’Davolio’,’Nancy’,’Sales Representative’,’Ms.’,convert(dateti
INSERT Employees(EmployeeID,LastName,FirstName,Title,TitleOfCourtesy,BirthDate,HireDate,
  VALUES(‘2′,’Fuller’,’Andrew’,’Vice President, Sales’,’Dr.’,convert(datet
INSERT Employees(EmployeeID,LastName,FirstName,Title,TitleOfCourtesy,BirthDate,HireDate,
  VALUES(‘3′,’Leverling’,’Janet’,’Sales Representative’,’Ms.’,convert(date
INSERT Employees(EmployeeID,LastName,FirstName,Title,TitleOfCourtesy,BirthDate,HireDate,
  VALUES(‘4′,’Peacock’,’Margaret’,’Sales Representative’,’Mrs.’,convert(da
INSERT Employees(EmployeeID,LastName,FirstName,Title,TitleOfCourtesy,BirthDate,HireDate,
  VALUES(‘5′,’Buchanan’,’Steven’,’Sales Manager’,’Mr.’,convert(datetime,’1
INSERT Employees(EmployeeID,LastName,FirstName,Title,TitleOfCourtesy,BirthDate,HireDate,
  VALUES(‘6′,’Suyama’,’Michael’,’Sales Representative’,’Mr.’,convert(datet
INSERT Employees(EmployeeID,LastName,FirstName,Title,TitleOfCourtesy,BirthDate,HireDate,
  VALUES(‘7′,’King’,’Robert’,’Sales Representative’,’Mr.’,convert(datetime
INSERT Employees(EmployeeID,LastName,FirstName,Title,TitleOfCourtesy,BirthDate,HireDate,
  VALUES(‘8′,’Callahan’,’Laura’,’Inside Sales Coordinator’,’Ms.’,convert(d
INSERT Employees(EmployeeID,LastName,FirstName,Title,TitleOfCourtesy,BirthDate,HireDate,
  VALUES(‘9′,’Dodsworth’,’Anne’,’Sales Representative’,’Ms.’,convert(datet


Here’s the script.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_InsertGenerator]') and 
OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_InsertGenerator]

CREATE PROC dbo.usp_InsertGenerator
(@tableName varchar(100), @where varchar(1000) = NULL, @order varchar(1000) = NULL) as


--Check whether the table has an identity column
DECLARE @TableHasIdentityColumn BIT
SELECT @TableHasIdentityColumn = 1
where TABLE_SCHEMA = 'dbo'
and COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1
AND TABLE_NAME = @tableName

IF ISNULL(@TableHasIdentityColumn, 0) = 1
    PRINT 'SET IDENTITY_INSERT ' + @tableName + ' ON'

--Declare a cursor to retrieve column specific information for the specified table
SELECT column_name,data_type FROM information_schema.columns WHERE table_name = @tableName
OPEN cursCol
DECLARE @string nvarchar(3000) --for storing the first half of INSERT statement
DECLARE @stringData nvarchar(3000) --for storing the data (VALUES) related statement
DECLARE @dataType nvarchar(1000) --data types returned for respective columns
SET @string='INSERT '+@tableName+'('
SET @stringData=''
DECLARE @colName nvarchar(50)
FETCH NEXT FROM cursCol INTO @colName,@dataType
IF @@fetch_status<>0
 print 'Table '+@tableName+' not found, processing skipped.'
 close curscol
 deallocate curscol
IF @dataType in ('varchar','char','nchar','nvarchar')
 SET @stringData=@stringData+''''+'''+isnull('''''+'''''+'+ @colName+'+'''''+''''',''NULL'')+'',''+'
if @dataType in ('text','ntext') --if the datatype is text or something else 
 SET @stringData=@stringData+'''''''''+isnull(cast('+ @colName+' as varchar(2000)),'''')+'''''',''+'
IF @dataType = 'money' --because money doesn't get converted from varchar implicitly
 SET @stringData=@stringData+'''convert(money,''''''+isnull(cast('+ @colName+' as varchar(200)),''0.0000'')+''''''),''+'
IF @dataType='datetime'
 SET @stringData=@stringData+'''convert(datetime,'+'''+isnull('''''+'''''+convert(varchar(200),'+ @colName+',121)+'''''+''''',''NULL'')+'',121),''+'
IF @dataType='image' 
 SET @stringData=@stringData+'''''''''+isnull(cast(convert(varbinary,'+ @colName+') as varchar(6)),''0'')+'''''',''+'
ELSE --presuming the data type is int,bit,numeric,decimal 
 SET @stringData=@stringData+''''+'''+isnull('''''+'''''+convert(varchar(200),'+ @colName+')+'''''+''''',''NULL'')+'',''+'
SET @string=@string+@colName+','
FETCH NEXT FROM cursCol INTO @colName,@dataType
DECLARE @Query nvarchar(4000)
SET @query ='SELECT '''+substring(@string,0,len(@string)) + ') VALUES(''+ ' + substring(@stringData,0,len(@stringData)-2)+'''+'')'' FROM '+@tableName
 SET @query = @query + ' where ' + @where
 SET @query = @query + ' order by ' + @order
exec sp_executesql @query
CLOSE cursCol

IF ISNULL(@TableHasIdentityColumn, 0) = 1
    PRINT 'SET IDENTITY_INSERT ' + @tableName + ' OFF'

Use SQL Server Trusted Connections with ASP.NET on Windows 2003 without impersonation

Access control and troubleshooting 401 errors must be one of the most annoying and recurring issues with configuring IIS.  One of the problems is that it’s often quite a long time between issues, and you simply forget how you solved something last time.  This time I decided to write it all down as I set things up.

Target Scenario
My target scenario is a local intranet, where you want to use a ‘service account’ to access SQL Server, directly from your ‘trusted’ web application, removing the need for impersonation. 

The benefits of this are of course that you can take advantage of connection pooling, but also removing the need to configure passwords in web.config for SQL users (or specific, impersonated domain users).  This also removed the overhead of configuring specific domain users and their SQL Server permissions.  It may also be that you just want to simplify your security model to work only on Windows authentication across the stack.  

SQL Server

  1. Create a new role in the database you’re accessing, for the purposes of your application
  2. Add your service domain user account to the role in SQL Server
  3. Assign permissions to objects, stored procedures etc to the role (not directly to the user)

IIS/Web Site

  1. Set up your web site/application as you would normally – one way to do things….
    1. Create your web application root folder on the web server
    2. Copy your files (or use your deployment tools/scripts to do this)
    3. Create a new application pool to house your new web application (probably model this from the default web site).  This is important as this is where the credentials will be set
    4. Create the new IIS web application against the root folder (if not already done as part of step 2)
    5. Associate the new IIS application with the new application pool
    6. Set the ASP.NET version of your IIS application appropriate (may need to restart IIS here)
  2. Ensure ‘Integrated Security’ is set ON in the Directory Security tab, and ‘Anonymous’ access is switched OFF
  3. Set the application pool’s ‘identity’ to the domain user you want to run the application (and connect to SQL Server) as
  4. Open a command window and Go to the windows\Microsoft.NET\Framework\vXXXXX folder
    1. run aspnet_regiis -ga <domain>\<user> to grant necessary access to the metabase etc for the service account (as per )
    2. In the command window go to the inetpub\adminscripts folder, and set
      NTAuthenticationHeaders metabase property as per instructions at  you can also use MetaEdit from
      the IIS Resource Kit to change this.  If you’re fully configured to use Kerberos then you can potentially skip this step, as it’s all about making IIS use NTLM authentication.
  5. Navigate to ‘Web Service Extensions’ in IIS Manager, and ensure that the ASP.NET version you’re targeting is ‘allowed’.  e.g. ASP.NET 4.0 is ‘prohibited’ by default.

So here we’ve circumvented the need to use impersonation by running the ASP.NET application as a specific domain user that is configured as a SQL Server Login, and granted the right access by means of a SQL Server role.  The main work is the plumbing to get IIS to work happily with that user in standard NTLM authentication (you may be able to use Kerberos depending on your network configuration).

Other background on creating service accounts can be found at

ASPNETCOMPILER The Target Directory could not be deleted. Please delete it manually or choose a different target – SubVersion

I’d added some projects to SubVersion today and later on added a CruiseControl.NET build.  I then started getting build failures due to the following:

ASPNETCOMPILER The Target Directory could not be deleted. Please delete it manually, or choose a different target

After a bit of looking around with the SysInternals Process Monitor I couldn’t find anything weird (e.g access denied due to someone locking the folder) and then saw that my output folder from the project (ASP.NET Web Deployment Project) was under SubVersion control (below)

Whoops! – after a swift delete of the folder (also in SubVersion) normality was resumed.  That’s another good reason not to put binaries into source control!

Setting up a Continuous Integration .NET Build Server without Visual Studio

We start with Windows XP SP2 (doesn’t really need to be a ‘server’).  My requirement is to rely on the .NET framework and open source tools – i.e. not requiring a Visual Studio Licence.  We’re using .NET 2.0 so everything will be in reference to that.

I assume here that you’ll be familiar with the actual software below and just want to get a Build Server up and running without having to install Visual Studio.  If you’re not familiar with Continuous Integration then start by looking at Martin Fowler’s Continuous Integration article and then the info on CruiseControl.NET, as that’s the tool that pulls everything together. 

There’s help on each of the sites below for installing and using each of the tools, so I won’t go into detail about each one.  The order of the list isn’t critical, but you’ll probably have some issues unless all are done.

  1. Install IIS from Add/Remove Windows Components (used for CruiseControl.NET) if not already present
  2. Open up a command prompt and run C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\aspnet_regiis.exe -i (to install ASP.NET in IIS)
  3. Install CruiseControl.NET (1.4) from  NOTE:  If you’re using a remote SubVersion repository (as in my case) then you’ll need to run the CruiseControl.NET Service as a domain user that has access to the network location of the repository  rather than LocalSystem.  This is because CruiseControl will be using the SVN client to poll for changes. 
  4. Install Microsoft .NET Framework 2.0 SP1 from
  5. Install latest SubVersion (1.5) from
  6. Install appropriate NUnit from (targeting .NET framework 2.0 – I have 2.4.6)
  7. Install latest FxCop from
  8. Install .NET Framework 2.0 SDK from (you’ll need this to get around a NAnt bug whereby it can’t resolve an internal property to load the .NET framework)
  9. Download latest NAnt from
  10. Download latest NAntContrib from (You’ll need this to run MSBuild)
  11. To save getting errors when building Web Projects (and other types) the easiest thing is to copy c:\program files\msbuild from your dev machine to the build server (otherwise you’ll have to alter the path of where the targets are in each project).

Once you’ve got all of these set up then you’ll be able to add some builds to your ccnet.config file ( most likely c:\program files\CruiseControl.NET\server\ccnet.config)

Test CruiseControl’s happy by going to http://localhost/ccnet It should come up OK but basically show ‘no projects’.

I’m not going to go into detail about setting up the builds here but may cover that in a follow up article, as it requires some more setup with SVN and a ‘standard’ project structure (to get benefit from reuse of build scripts).  If you’re still with me then I’ll share one last thing which might help… 

Some people choose to have NAnt in a standard place and just reference it from the PATH, but I now use the power of svn:externals to drag in NAnt, NUnit and other common external dependencies like MS Enterprise Library from a shared SVN location to each project.  This means you just get latest of a project and it has ‘everything’ needed to build – no installations or assumptions about locations of tools.

NAnt Solution Task bug – Error checking whether ‘xxx’ is an enterprise template project.

Just spent a little while looking into this.  I thought I’d be clever and add my build script to the solution it builds so I can get some intellisense from Resharper (although it’s somewhat limited).  This added the file into ‘Solution Items’ which is a virtual folder.

The error appears because NAnt expects to find valid project files in the solution folders.  I had the same error previously with Web Site Projects (as these are also ‘special folders’). 

I’m using NAnt 0.86 (Build 0.86.2898.0; beta1; 8/12/2007), and from the bug report it seems that it’s fixed in 0.86 beta 2 (not yet out) so you’ll have to get a nightly build if you’re desperate.

My workaround for the moment is to use the NAntContrib MSBuild task to build my Web Deployment project (which in turn builds all the dependencies), as follows:

<target name=”build.webdeploy” description=”Compiles a Web Deployment Project using MSBuild”>
  <msbuild project=”${project.dir}\mydeploydir\mydeployproj.wdproj” verbosity=”Detailed” >
     <arg value=”/p:Configuration=${build.configuration}” />

You can fill in the blanks yourself with the parameters.

Generate SQL Inserts from existing data

This is one from the archives that obviously got away.  I had need for this today so thought I’d post so I don’t have to look so hard next time!

It’s pretty much a case of create the SP in your chosen database and run with a tablename as the parameter.  I originally found this on one of the SQL sites (probably SQLServerCentral), and amended it slightly to add some basic filtering in the @where parameter.

There’s a few limitations from memory – with blob, image and text fields, but for your average tables this will probably do the trick just fine.

I’ve also used a different method that basically builds up a single INSERT with SELECT ‘literal values’ UNION SELECT ‘literal values’.  That works fine and is potentially easier to control transaction-wise as everthing’s in a single statement.  Anyway here’s the code….

if exists (select from dbo.sysobjects where id = object_id(N'[dbo].[usp_InsertGenerator]’and OBJECTPROPERTY(idN’IsProcedure’1)
drop procedure [dbo].[usp_InsertGenerator]

CREATE PROC dbo.usp_InsertGenerator
varchar(100), @where varchar(1000= NULLas
–Declare a cursor to retrieve column specific information for the specified table
column_name,data_type FROM information_schema.columns WHERE table_name @tableName
OPEN cursCol
DECLARE @string nvarchar(3000–for storing the first half of INSERT statement
DECLARE @stringData nvarchar(3000–for storing the data (VALUES) related statement
DECLARE @dataType nvarchar(1000–data types returned for respective columns
SET @string=‘INSERT ‘+@tableName+‘(‘
SET @stringData=
DECLARE @colName nvarchar(50)
FETCH NEXT FROM cursCol INTO @colName,@dataType
IF @@fetch_status<>0
‘Table ‘+@tableName+‘ not found, processing skipped.’
close curscol
deallocate curscol
@dataType in (‘varchar’,‘char’,‘nchar’,‘nvarchar’)
@dataType in (‘text’,‘ntext’–if the datatype is text or something else 
@stringData=@stringData+””””’+isnull(cast(‘+@colName+‘ as varchar(2000)),””)+”””,”+’
@dataType ‘money’ –because money doesn’t get converted from varchar implicitly
@stringData=@stringData+”’convert(money,”””+isnull(cast(‘+@colName+‘ as varchar(200)),”0.0000”)+”””),”+’
@stringData=@stringData+””””’+isnull(cast(convert(varbinary,’+@colName+‘) as varchar(6)),”0”)+”””,”+’
–presuming the data type is int,bit,numeric,decimal 
FETCH NEXT FROM cursCol INTO @colName,@dataType
@Query nvarchar(4000)
SET @query =‘SELECT ”’+substring(@string,0,len(@string)) + ‘) VALUES(”+ ‘ substring(@stringData,0,len(@stringData)-2)+”’+”)” FROM ‘+@tableName
@query @query + ‘ where ‘ + @where
exec sp_executesql @query
CLOSE cursCol

Colorized by: CarlosAg.CodeColorizer

Getting the most out of ASP.NET Web Deployment Projects

In my ongoing love (but mostly) hate relationship with ASP.NET Web ‘Site’s’ I’ve been using Web Deployment projects to make things more bearable. 

I currently swap in connection strings from 3 files – one for each build configuration (debug, test, release – connectionstrings.debug.config etc ).  This works fine as per the doco on WDP.  I use CruiseControl.NET and NAnt to automate builds, and a few nagging ‘automated’ pieces were missing from the puzzle. 

  1. Encryption of connectionStrings (or other web.config sections that you want to protect) – without affecting the ‘source’ file.  I’ve assumed here that ‘internal’ people are trusted. 
  2. Changing of other config stuff (like debug=false) in the test and release builds.  (My attempts to get this to work had previously failed as you don’t seem to be able to specify system.web as a replaceable section.
  3. Encrypting Forms authentication passwords, using MD5 hash.  This isn’t difficult, I just didn’t have a tool to generate the hash value.

Encryption of config sections

OK – after re-reading Scott Gu’s post on Web Deployment Projects, and K. Scott Allen’s post on how to simply encrypt sections of config files, I realised that I could just add a post-build event (manually) in the wdproj file (right-click in solution explorer –> open project file). 

The build events are already in but commented at the bottom of the file.  I ended up with

<Target Name=”AfterBuild”>
<Exec WorkingDirectory=”$(OutputPath)” 
Command=”C:\windows\Microsoft.NET\Framework\v2.0.50727\aspnet_regiis -pef connectionStrings .”
<!– Also remove our ‘source’ config files using the del command rather than the delete task as you
have to jump through hoops to specify wildcards –>
<Exec WorkingDirectory=”$(OutputPath)” Command=”del compilation*.config” />
<Exec WorkingDirectory=”$(OutputPath)” Command=”del connectionstrings*.config” />


If I put this anywhere other than ‘AfterBuild’ it didn’t seem to do anything.  I certainly learnt a bit more about aspnet_regiis, as I’d only used it previously to ‘install’ ASP.NET.  I also had to specify the path to aspnet_regiis, but you could obviously use a property for this (I’m new to MSBuild – only dipping in when I have to, so the framework path may already be a standard property?).

Replacement of system.web sections

The key thing here (which I don’t believe was documented very well anywhere) is how to replace system.web elements.  Other typical replacements – e.g. appSettings or connectionStrings are children of the root config element.  You’d therefore assume that you need to replace the whole of system.web (which is a little inconvenient – but still worth it).  This doesn’t work and you’ll get a ‘nice’ WDP00002 error saying it can’t find the system.web element (a bit like saying ‘can’t find printer’ when it’s right next to the computer!). 

You just have to go one level down as follows (in the Deployment –> Web.config file section replacements property page):


compilation.release.config may be as simple as…

<compilation debug=”false”></compilation>

You might have a warning saying ‘compilation’ isn’t a valid element, but this is just the intellisense barking as it validates against the config schema.

Encrypting Forms Authentication Passwords

This is pretty simple and there’s lots of docs to support this, but I wanted a simple tool to generate the hash for a given string, and a quick google yielded a nice little command-line tool

This way you can plug it into your build if you need to, but also replace for different environments using the techniques above.

Dynamic FTP Scripts

I seem to be getting back to basics at the moment with some of the things I’m doing – and didn’t imagine myself having to get an automated FTP update working this week…

My objective was to ‘get’ files from a remote machine using a folder pattern consisting of the date in an ‘yymmdd’ format.  I’d forgotten that script files used with ftp (ftp -s:myscript.txt) are dumb text files, and if you want to ‘get’ from a dynamic location – or dynamic filenames you need to magic something up.

My interest was sparked here, and although I found a C# FTP library on CodeProject that would clearly do the job, and would be cool to use in itself, I was more interested in using ‘old skool’ lo-tech methods to solve the problem.

I read one article that suggested using batch files to spit out the ftp script.  Excellent idea I thought – so this is what I ended up with…. (I’ve changed a few details to protect the innocent so if it doesn’t quite work it’s only due to my own typo 🙂 )

I won’t explain each bit as that would spoil the fun – but the general gist is that FTPReportsForDate is normally run without parameters and it finds files in a specific folder and renames them on the local host.  The process also does other stuff I didn’t have time to go into….

FTPReportsForDate.cmd (which accepts a parameter or gets current date if not supplied)

@echo off

echo Creating Log Folder
IF NOT EXIST C:\logs\FTP\ md c:\logs\FTP

echo Removing previous processing date
IF EXIST processingdate.txt del processingdate.txt /F

IF “%1” == “” (
 echo Getting current processing date
 cscript //NoLogo GetCurrentDate.vbs >> processingdate.txt
 echo Transferring File
 FOR /F %%f in (processingdate.txt) do CALL FTPCommand.cmd %%f

IF “%1” NEQ == “” (
 echo Transferring File
 CALL FTPCommand.cmd %1

FTPCommand.cmd  This constructs the FTP script file and does the FTP itself…

IF EXIST FTPLatestFile.txt del FTPLatestFile.txt /F

Type FTPHeader.txt >> FTPLatestFile.txt
echo cd RP%1.IN >> FTPLatestFile.txt
echo get staticfilename.fil dyn%1.fil >> FTPLatestFile.txt
echo close >> FTPLatestFile.txt
echo bye >> FTPLatestFile.txt

ftp -s:FTPLatestFile.txt >> c:\logs\ftp\transfer_%1.log

FTPHeader.txt This contains the static information used in the FTP script

lcd c:\incoming

gets current date in a yymmdd format in the event we don’t pass in a date to FTPReportsForDate.cmd

‘Simple script to output todays date in yymmdd format for FTP processing

Function pd(n, totalDigits)
 if totalDigits > len(n) then
  pd = String(totalDigits-len(n),”0″) & n
  pd = n
 end if
End Function

Wscript.echo pd(RIGHT(YEAR(date()),2),2) & pd(MONTH(date()),2) & pd(DAY(date()),2)

Using Batch Build in Visual Studio

This is a really simple one, but if you want to build a solution in a specific ‘Configuration’ and you have a big solution open, it can sometimes be an awfully long time to switch configurations – e.g. Debug -> Release.  You may find yourself needing to do this when you’re needing to build and rebuild setup projects whilst testing.

You could of course run a command line compile, but a simple way to stay in the IDE and Debug configuration whilst running a Release build is the Batch Build facility. 

Just go to Build –> Batch Build, then select all the Release versions of the projects you want to build (or whatever you want to build), and then click Build.  The settings will be remembered for the next time you go in too…

I said it was simple.