Click here to Skip to main content
15,867,686 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

 
GeneralRe: Reserved words as field names Pin
Phreud14-Dec-06 13:53
Phreud14-Dec-06 13:53 
GeneralInternational date formats Pin
vincebowdren7-Dec-06 1:12
vincebowdren7-Dec-06 1:12 
GeneralRe: International date formats Pin
Chris Hambleton7-Dec-06 8:04
Chris Hambleton7-Dec-06 8:04 
GeneralWhy not Pin
icestatue5-Dec-06 11:24
icestatue5-Dec-06 11:24 
GeneralRe: Why not Pin
Chris Hambleton7-Dec-06 8:06
Chris Hambleton7-Dec-06 8:06 
GeneralDbCommandBuilder Pin
zeleps5-Dec-06 9:05
zeleps5-Dec-06 9:05 
GeneralRe: DbCommandBuilder Pin
Chris Hambleton7-Dec-06 8:21
Chris Hambleton7-Dec-06 8:21 
GeneralMissing support for binary data Pin
thomas_z4-Dec-06 8:46
thomas_z4-Dec-06 8:46 
GeneralRe: Missing support for binary data Pin
Chris Hambleton4-Dec-06 10:21
Chris Hambleton4-Dec-06 10:21 
GeneralNot working for table owners/schemas other than dbo Pin
bworld4-Dec-06 6:50
bworld4-Dec-06 6:50 
GeneralRe: Not working for table owners/schemas other than dbo Pin
Chris Hambleton7-Dec-06 8:22
Chris Hambleton7-Dec-06 8:22 
GeneralPerformance Issue Pin
BuddyWork2-Dec-06 22:25
BuddyWork2-Dec-06 22:25 
GeneralRe: Performance Issue Pin
Pop Catalin3-Dec-06 21:28
Pop Catalin3-Dec-06 21:28 

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.