Generating SQL

Generally, I don’t like writing code more than once if I can avoid it.  Most people apply the DRY principle to application code, but I also find it a good idea to follow this in writing SQL administration code.   Most applications today store a significant amount of administration level data in the database so the support team can quickly change the behavior of a system without having to redeploy the application.  Although beneficial, it does require someone to write SQL to code insert and update values in the database, and depending on the amount necessary can be time consuming, error prone, and tedious.

To handle tasks which require writing several (In my case hundreds) of SQL Insert statements at once, I created the following script.

Use it by creating an accompanying file like so:

The crux behind the script is the GenerateInsertStatement. It takes the table name as a string and then a sequence of Entry.

To handle multiple inserts all you need do is create a sequence of sequences:

Sometimes you need to insert data into a table with an identity column. This requires wrapping the statement in using the Identity Insert options. The generation script has a function which will wrap the sql code in an identity insert for the table. Use the AddIdentityInsertWrapper like so:

Leave a Reply