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 Results

DELETE Statements and Results

UPDATE Statements

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).

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.

Comments and Discussions

The code has been updated with most of the changes/enhancements suggested since the initial posting. The only change that wasn't added was the ability to browse tables/databases from a non-default schema (something other than 'dbo'.

During my testing, I was able to browse two different databases/tables that were of a schema other than dbo and it worked fine. What could be an issue is that the schema is not included in the statements that are generated, and this will probably be left for another enhancement...

I wrote a perl to do this stuff. Ever since I wrote it, I've found that I need to constantly tweak it to do yet another slightly more complex task. Heh. Anywho... I briefly glanced accross your code and don't see that you handle embedded tics in STRING data. STRING (varchar, etc.) data like this:
column name |column type |column data
fullname |varchar(40) |O'Reiley

When you generate an INSERT for this data, the generated text may likely look like:
'O'Reiley'

This will cause T-SQL errors. The correct INSERT text needs to have all (inner) tics doubled. The first tic acts as a metacharacter switch, for the second tic.
'O''Reiley'

Just FYI, sorry if you already covered this, I cannot compile your solution because I only have VS .NET 2003 installed (for work) on my machine, and this version complains that the sln was created with a newer version and dies.

Where it generates sql with a date, I think it needs to specify the date in an unambiguous format, to cope with internationalisation issues. I'm working in the UK, and it is generating sql statements with date formats thus:INSERT INTO AmsClinicStreamFrozen (ClinicDate) VALUES ('14/12/2006 00:00:00')Now 14/12/2006 (14th December) is the date I was after, but when I try and run this query mssql returns this error:"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."This could be avoided by stating dates in an unambiguous format, for example:INSERT INTO AmsClinicStreamFrozen (ClinicDate) VALUES ('14 December 2006 00:00:00')

I have seen DbCommandBuilder, and have used it in other projects. The approach of this SQL Statement Generator was to act upon any DataTable (even datatables that came from and XML file) and dump the SQL to a text file, and not just transfer data between two databases.

In several companies I've worked for, we had to dump and then tweek large amounts of SQL statements from one database to another, and have a paper/source trail of the changes made. In those circumstances/constraints, using the DbCommandBuilder to transfer directly between two databases was not really an option. However, it could be used to just create all the SQL commands and have them dumped to a text file...