Click here to Skip to main content
15,867,594 members
Articles / Programming Languages / SQL
Article

SQL Statement Generator

Rate me:
Please Sign up or sign in to vote.
4.78/5 (45 votes)
14 Dec 20063 min read 253.4K   6.8K   148   57
A utility that generates INSERT, UPDATE, or DELETE SQL statements.

Sample Image - screen0.jpg

Introduction

The SQL Statement Generator is a Windows application that loads a DataTable from the specified query and then generates INSERT, UPDATE, or DELETE SQL statements based upon the type of SQL command and the selected columns of the DataTable.

Standard SQL queries can be specified to construct the precise DataTable of the data to generate the SQL statements for. Also, the name of the target table and the columns of the resultant generated SQL statements can be selected to contruct the exact SQL statements you want to generate.

Background

I'll admit it - I hate writing INSERT SQL statements, especially when it involves tables of more than three columns and two-three rows (a rarity...not!). I had a task that involved migrating data from a poorly-architected database to a new database, and later the data would need to be exported again to the production database after the development and testing phases were completed. Initially, this utility only handled INSERT statements, and then was expanded to also include UPDATE and DELETE SQL statements.

Using the Application

The SQL Statement Generator is quick and easy to use - just select the database and/or table/SQL query and the load the results into the grid. Then specify the type of SQL statement to generate and the columns to include in the SQL statements, click the GENERATE button, and voila! - instant SQL statements!

For INSERT statements, all the columns in the result-set are auto-selected by default, except for the zeroth column (since that is typically the primary key).

For UPDATE statements, all the columns in the result-set are auto-selected by default, except for the zeroth column (similar to the INSERT statements). However, the generation process for UPDATE statements has an added step - the WHERE column(s) for the resulting SQL statements need to be specified, because the identity value of the primary keys between the same two tables in different databases is usually different.

For DELETE statements, only the zeroth column in the result-set is auto-selected by default, since DELETEs typically use the primary key to identify which records to delete. Deleting rows by a complex WHERE clause is not always the safest method...

Note: The SQL Server to enumerate the databases and their tables from is specified in the configuration file, and you may need to leave off the Database/Initial Catalog attribute, since all the databases in the datasource/SQL Server are enumerated.

SQL Statement Generator Examples

INSERT Statements

insert

INSERT Results

insert-results

DELETE Statements and Results

deletes

UPDATE Statements

update-where

UPDATE Results

update-results

The SQL Statement Generator Engine - How It Works

The main engine/processor of the SQL Statement Generator is the SqlScriptGenerator class, which reads the columns of the DataTable and generates the SQL statements based upon the columns selected by the user. Once all the SQL statements are generated, they are dumped to a temp file and then displayed in a text editor (Notepad, by default).

C#
public class SqlScriptGenerator
{
    public static string GenerateSqlInserts(ArrayList aryColumns, 
                                            DataTable dtTable, 
                                            string sTargetTableName)
    {
        string sSqlInserts = string.Empty;
        StringBuilder sbSqlStatements = new StringBuilder(string.Empty);

        // create the columns portion of the INSERT statement
        string sColumns = string.Empty;
        foreach (string colname in aryColumns)
        {
            if (sColumns != string.Empty)
                sColumns += ", ";

            sColumns += colname;
        }

        // loop thru each record of the datatable
        foreach (DataRow drow in dtTable.Rows)
        {
            // loop thru each column, and include
            // the value if the column is in the array
            string sValues = string.Empty;
            foreach (string col in aryColumns)
            {
                if (sValues != string.Empty)
                    sValues += ", ";

                // need to do a case to check the column-value types
                // (quote strings(check for dups first), convert bools)
                string sType = string.Empty;
                try
                {
                    sType = drow[col].GetType().ToString();
                    switch (sType.Trim().ToLower())
                    {
                        case "system.boolean":
                            sValues += (Convert.ToBoolean(drow[col]) 
                                        == true ? "1" : "0");
                            break;

                        case "system.string":
                            sValues += string.Format("'{0}'", 
                                       QuoteSQLString(drow[col]));
                            break;

                        case "system.datetime":
                            sValues += string.Format("'{0}'", 
                                       QuoteSQLString(drow[col]));
                            break;

                        default:
                            if (drow[col] == System.DBNull.Value)
                                sValues += "NULL";
                            else
                                sValues += Convert.ToString(drow[col]);
                            break;
                    }
                }
                catch
                {
                    sValues += string.Format("'{0}'", 
                               QuoteSQLString(drow[col]));
                }
            }

            //   INSERT INTO Tabs(Name) 
            //      VALUES('Referrals')
            // write the insert line out to the stringbuilder
            string snewsql = string.Format("INSERT INTO {0}({1}) ", 
                                            sTargetTableName, sColumns);
            sbSqlStatements.Append(snewsql);
            sbSqlStatements.AppendLine();
            sbSqlStatements.Append('\t');
            snewsql = string.Format("VALUES({0});", sValues);
            sbSqlStatements.Append(snewsql);
            sbSqlStatements.AppendLine();
            sbSqlStatements.AppendLine();
        }

        sSqlInserts = sbSqlStatements.ToString();
        return sSqlInserts;
    }

    public static string GenerateSqlUpdates(ArrayList aryColumns, 
                         ArrayList aryWhereColumns, 
                         DataTable dtTable, string sTargetTableName)
    {
        string sSqlUpdates = string.Empty;
        StringBuilder sbSqlStatements = new StringBuilder(string.Empty);

        // UPDATE table SET col1 = 3, col2 = 4 WHERE (select cols)
        // loop thru each record of the datatable
        foreach (DataRow drow in dtTable.Rows)
        {
            // VALUES clause:  loop thru each column, and include 
            // the value if the column is in the array
            string sValues = string.Empty;
            foreach (string col in aryColumns)
            {
                string sNewValue = col + " = ";
                if (sValues != string.Empty)
                    sValues += ", ";

                // need to do a case to check the column-value types 
                // (quote strings(check for dups first), convert bools)
                string sType = string.Empty;
                try
                {
                    sType = drow[col].GetType().ToString();
                    switch (sType.Trim().ToLower())
                    {
                        case "system.boolean":
                            sNewValue += (Convert.ToBoolean(drow[col]) ==
                                          true ? "1" : "0");
                            break;

                        case "system.string":
                            sNewValue += string.Format("'{0}'", 
                                         QuoteSQLString(drow[col]));
                            break;

                        case "system.datetime":
                            sNewValue += string.Format("'{0}'", 
                                         QuoteSQLString(drow[col]));
                            break;

                        default:
                            if (drow[col] == System.DBNull.Value)
                                sNewValue += "NULL";
                            else
                                sNewValue += Convert.ToString(drow[col]);
                            break;
                    }
                }
                catch
                {
                    sNewValue += string.Format("'{0}'", 
                                 QuoteSQLString(drow[col]));
                }

                sValues += sNewValue;
            }

            // WHERE clause: loop thru each column, and include
            // the value if column is in array
            string sWhereValues = string.Empty;
            foreach (string col in aryWhereColumns)
            {
                string sNewValue = col + " = ";
                if (sWhereValues != string.Empty)
                    sWhereValues += " AND ";

                // need to do a case to check the column-value types
                // (quote strings(check for dups first), convert bools)
                string sType = string.Empty;
                try
                {
                    sType = drow[col].GetType().ToString();
                    switch (sType.Trim().ToLower())
                    {
                        case "system.boolean":
                            sNewValue += (Convert.ToBoolean(drow[col]) == 
                                          true ? "1" : "0");
                            break;

                        case "system.string":
                            sNewValue += string.Format("'{0}'", 
                                         QuoteSQLString(drow[col]));
                            break;

                        case "system.datetime":
                            sNewValue += string.Format("'{0}'", 
                                         QuoteSQLString(drow[col]));
                            break;

                        default:
                            if (drow[col] == System.DBNull.Value)
                                sNewValue += "NULL";
                            else
                                sNewValue += Convert.ToString(drow[col]);
                            break;
                    }
                }
                catch
                {
                    sNewValue += string.Format("'{0}'", 
                                 QuoteSQLString(drow[col]));
                }

                sWhereValues += sNewValue;
            }

            // UPDATE table SET col1 = 3, col2 = 4 WHERE (select cols)
            // write the line out to the stringbuilder
            string snewsql = string.Format("UPDATE {0} SET {1} WHERE {2};", 
                                            sTargetTableName, sValues, 
                                            sWhereValues);
            sbSqlStatements.Append(snewsql);
            sbSqlStatements.AppendLine();
            sbSqlStatements.AppendLine();
        }

        sSqlUpdates = sbSqlStatements.ToString();
        return sSqlUpdates;
    }

    public static string GenerateSqlDeletes(ArrayList aryColumns, 
                                            DataTable dtTable, 
                                            string sTargetTableName)
    {
        string sSqlDeletes = string.Empty;
        StringBuilder sbSqlStatements = new StringBuilder(string.Empty);

        // loop thru each record of the datatable
        foreach (DataRow drow in dtTable.Rows)
        {
            // loop thru each column, and include 
            // the value if the column is in the array
            string sValues = string.Empty;
            foreach (string col in aryColumns)
            {
                string sNewValue = col + " = ";
                if (sValues != string.Empty)
                    sValues += " AND ";

                // need to do a case to check the column-value types
                // (quote strings(check for dups first), convert bools)
                string sType = string.Empty;
                try
                {
                    sType = drow[col].GetType().ToString();
                    switch (sType.Trim().ToLower())
                    {
                        case "system.boolean":
                            sNewValue += (Convert.ToBoolean(drow[col]) ==
                                          true ? "1" : "0");
                            break;

                        case "system.string":
                            sNewValue += string.Format("'{0}'", 
                                         QuoteSQLString(drow[col]));
                            break;

                        case "system.datetime":
                            sNewValue += string.Format("'{0}'", 
                                         QuoteSQLString(drow[col]));
                            break;

                        default:
                            if (drow[col] == System.DBNull.Value)
                                sNewValue += "NULL";
                            else
                                sNewValue += Convert.ToString(drow[col]);
                            break;
                    }
                }
                catch
                {
                    sNewValue += string.Format("'{0}'", 
                                 QuoteSQLString(drow[col]));
                }

                sValues += sNewValue;
            }

            // DELETE FROM table WHERE col1 = 3 AND col2 = '4'
            // write the line out to the stringbuilder
            string snewsql = string.Format("DELETE FROM {0} WHERE {1};", 
                                            sTargetTableName, sValues);
            sbSqlStatements.Append(snewsql);
            sbSqlStatements.AppendLine();
            sbSqlStatements.AppendLine();
        }

        sSqlDeletes = sbSqlStatements.ToString();
        return sSqlDeletes;
    }

    public static string QuoteSQLString(object ostr)
    {
        return ostr.ToString().Replace("'", "''");
    }
    
    public static string QuoteSQLString(string str)
    {
        return str.Replace("'", "''");
    }
}

Conclusion

I hope you find this article and utility useful - it has made working with SQL statements and data migration a snap! Enjoy!

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
Software Developer
United States United States
SOFTWARE: Chris Hambleton is a Software Developer with proven experience in developing both web and Windows client-server applications with WPF, ASP.NET, C#, SQL Server, VB.NET, Visual C++, and VB6.

Chris's website is at ChrisHambleton.com and he has a small web-hosting/consulting business called CustomersInFocus.com. He has several other websites such as EzekielWatch.com, iWriterPro.com, and BookBlitzer.com.

WRITING: He has also written several fiction books ("The Time of Jacob's Trouble" and "Endeavor in Time"), available at CWHambleton.com and of course, at Amazon.com (Full Amazon Profile).

Comments and Discussions

 
QuestionI still use it Pin
Jacquers11-Oct-18 22:49
Jacquers11-Oct-18 22:49 
PraiseIt just works. Pin
rroger12-May-18 0:17
rroger12-May-18 0:17 
Questionnot working error Pin
K3yb0ard1-Apr-15 4:59
K3yb0ard1-Apr-15 4:59 
AnswerRe: not working error Pin
Jacquers4-Jul-16 2:36
Jacquers4-Jul-16 2:36 
QuestionJust The Ticket! Pin
Barry W Novak14-Mar-14 9:44
Barry W Novak14-Mar-14 9:44 
GeneralMy vote of 5 Pin
Daqdibi23-Mar-13 23:00
Daqdibi23-Mar-13 23:00 
GeneralMy vote of 5 Pin
Kanasz Robert26-Sep-12 4:59
professionalKanasz Robert26-Sep-12 4:59 
QuestionMy Vote of 5 Pin
SaranRaj 2718-Aug-12 9:34
SaranRaj 2718-Aug-12 9:34 
Generalawsome- 10 points out of 10 Pin
darshan.pandya868-Aug-12 19:38
darshan.pandya868-Aug-12 19:38 
GeneralMy vote of 5 Pin
Vanlalruata Hnamte22-Jun-12 6:05
Vanlalruata Hnamte22-Jun-12 6:05 
GeneralWorking Like Charm Pin
Vanlalruata Hnamte22-Jun-12 6:04
Vanlalruata Hnamte22-Jun-12 6:04 
QuestionIt's Working Pin
Vanlalruata Hnamte22-Jun-12 6:02
Vanlalruata Hnamte22-Jun-12 6:02 
QuestionThanks! Saved me from RedGate Pin
RS Reddy220-Apr-12 12:31
RS Reddy220-Apr-12 12:31 
QuestionFix for Guid and Time fields Pin
Jacquers14-Mar-12 21:34
Jacquers14-Mar-12 21:34 
Questionconsidering locale... Pin
roberto galbiati15-Dec-11 5:32
professionalroberto galbiati15-Dec-11 5:32 
GeneralHi Vote 5 out of 5 Pin
Rohit_kakria15-May-11 5:00
Rohit_kakria15-May-11 5:00 
GeneralGreat Tool! Pin
MS86TO18-Feb-11 9:14
MS86TO18-Feb-11 9:14 
GeneralMy vote of 4 Pin
Jacquers15-Feb-11 21:48
Jacquers15-Feb-11 21:48 
Very useful app, thanks! Just needs a fix for handling Guid / uniqueidentifier types (missing the quotes '').
GeneralMy vote of 5 Pin
ruanmyv20-Oct-10 5:59
ruanmyv20-Oct-10 5:59 
Generalgood program Pin
Jessie_blue_camote25-Jul-10 2:02
Jessie_blue_camote25-Jul-10 2:02 
GeneralNice Pin
Muneer Abdullah1-Jul-10 2:35
Muneer Abdullah1-Jul-10 2:35 
GeneralAwesome tool. Many thanks!!! Pin
JacktheRipper7-Jun-10 0:08
JacktheRipper7-Jun-10 0:08 
GeneralThank you for the idea Pin
Hunty18-Feb-10 6:07
Hunty18-Feb-10 6:07 
GeneralRe: Thank you for the idea Pin
Chris Hambleton18-Feb-10 7:22
Chris Hambleton18-Feb-10 7:22 
GeneralYou got 5 out of 5 Pin
Rohit_kakria10-Feb-10 0:18
Rohit_kakria10-Feb-10 0:18 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.