I finally got sick and tired of this effort and the limitations that I found. So, in my own spare time over last weekend, I dropped into developer mode and wrote a little C# utility that did what I wanted.
The code below was written very quickly. I didn't use StringBuilder, and I did not try to optimize. This is quick and dirty ... and it works just great.
You can use this code for personal use. You cannot reprint this code anywhere without my permission. Feel free to contact me if you have any enhancements.
Sorry for some of the formatting stuff. In particular, the nice line spacing that I use will not show up on the web page.
-marc
using System;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.IO;
namespace SQLGenerateInsertStatements
{
/// <summary>
/// This was written by Marc Adler (magmasystems at yahoo dot com).
/// This code can be used freely for your own personal use.
/// It may not be reprinted anywhere without permission of Marc Adler.
/// </summary>
static class Program
{
[STAThread]
static void Main(string[] args)
{
string databaseName = null;
string tableName = null;
string connectionString = "Data Source=(local);Initial Catalog={d};Integrated Security=True";
string outputFile = @"c:\GeneratedStatements.sql";
if (args == null args.Length == 0)
{
Usage();
return;
}
for (int i = 0; i < args.Length; i++)
{
string arg = args[i].ToLower();
if (arg == "-table")
{
tableName = args[++i];
}
else if (arg == "-database")
{
databaseName = args[++i];
}
else if (arg == "-output")
{
outputFile = args[++i];
}
else if (arg == "-connectionstring")
{
connectionString = args[++i];
}
else
{
Usage();
return;
}
}
if (string.IsNullOrEmpty(tableName) string.IsNullOrEmpty(databaseName))
{
Console.WriteLine("The table name or the database name was not specified.\n");
Usage();
return;
}
if (connectionString.Contains("{d}"))
connectionString = connectionString.Replace("{d}", databaseName);
try
{
GetData(databaseName, tableName, connectionString, outputFile);
}
catch (Exception exc)
{
Console.WriteLine(exc.Message);
}
}
static private void Usage()
{
Console.WriteLine("SQLGenerateInsertStatements [-help] [-table <tablename>] [-database <databasename>] [-connectionstring <connstring>] [-output <outputfile>]");
Console.WriteLine(@"The default output file is C:\GeneratedStatements.sql");
Console.WriteLine("If the connection string has '{d}' embedded in it, the '{d}' is replaced with the database name.");
Console.WriteLine("If the outputfile string has '{d}' embedded in it, the '{d}' is replaced with today's date.");
}
static private void GetData(string databaseName, string tableName, string connectionString, string outputFile)
{
// Get the (optional) name of the file to write the SQL statements to
if (outputFile.IndexOf("{d}") >= 0)
{
outputFile = outputFile.Replace("{d}", DateTime.Now.ToString("d", new CultureInfo("de-DE")));
}
StreamWriter outputStream = new StreamWriter(outputFile);
// Write the "USE database" statement
outputStream.WriteLine(string.Format("USE [{0}]", databaseName));
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand())
{
// Initialize the SQL Connection
command.Connection = connection;
command.CommandText = "SELECT * FROM " + tableName;
connection.Open();
// Get a DataReader
using (SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
{
// Format the first part of the INSERT statement. This part remains
// constant for every row that is generated.
string sInsert = string.Format("INSERT INTO [{0}] ( ", tableName);
for (int iCol = 0; iCol < reader.FieldCount; iCol++)
{
sInsert += string.Format("[{0}]", reader.GetName(iCol));
if (iCol < reader.FieldCount - 1)
sInsert += ",";
}
sInsert += ") VALUES ({0})";
// Read each row of the table
object[] objs = new object[reader.FieldCount];
int nLines = 1;
while (reader.Read())
{
int n = reader.GetValues(objs);
string sValues = string.Empty;
// Go through each column of the row, and generate a string
for (int i = 0; i < n; i++)
{
try
{
string sVal = (reader.IsDBNull(i)) ? "null" : ObjectToSQLString(objs[i]);
sValues += sVal;
if (i < n - 1)
sValues += ",";
}
catch (DataException)
{
Console.WriteLine(string.Format("Conversion error in Record {0}, Column {1}", nLines, reader.GetName(i)));
return;
}
}
// Dump the INSERT statement to the file
outputStream.WriteLine(string.Format(sInsert, sValues));
nLines++;
}
}
}
}
outputStream.Flush();
outputStream.Close();
}
static string ObjectToSQLString(object o)
{
if (o == null o == DBNull.Value)
return "null";
Type t = o.GetType();
if (t == typeof (string))
return string.Format("'{0}'", ((string) o).Trim());
if (t == typeof (int))
return ((int) o).ToString();
if (t == typeof (long))
return ((long) o).ToString();
if (t == typeof (float))
return ((float) o).ToString();
if (t == typeof (double))
return ((double) o).ToString();
if (t == typeof (bool))
return ((bool) o).ToString();
if (t == typeof (DateTime))
return string.Format("'{0}'", ((DateTime) o));
throw new DataException();
}
}
}
