Generate SQL INSERT Statements for a SS2005 Table

For quite a while, I have been looking for a utility that would be able to take a large table stored in SQL Server 2005, and generate INSERT statements. I recently tried a few SQL scripts that I found on the web. Sometimes they worked, sometimes they didn't. But, they all had a problem in the fact that the generated statements were dumped to a SQL Results pane. If you had a very large table, then you had trouble cutting and pasting these statements to your favorite text editor.

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();
        }
    }
}