One of the most pain-staking tasks a developer and DBA must often accomplish
is to create an install procedure for the database. This involves creating the
necessary DDL to create the tables, relationships and indexes. Inevitably in
this process you forget about the data. How are you going to transport the data
from your testing environment to production and to the client. Over the past
month at my "real job", I used AutoInserts 1.19, by
LockwoodTech to accomplish this task.
AutoInserts provides a GUI method to easily read a database and create INSERT
statements from it. The nicest thing about the product is that it automatically
reads your referential integrity and loads the tables in order.
Using the application is extremely simple. You can have a well-commented,
clean script in 3 steps. First, create a connection to your source database. You
have the option of reading a DSN or connecting the old-fashion way, which I
demonstrate below. Leaving the authentication area blank will have the
application login using your Windows account.
Upon connecting, you are presented with a list of tables in the database. The
application reports the number of records in the table and how large the table
is in MB. It also shows you how large each row is in bytes.. Most importantly,
the Order column shows you which order the tables will be inserted in. The
higher the number, the sooner it will be loaded. One of the limitations
I've noticed here is that views are not available to the application.
There are a number of options that I like to set as well before generating
the INSERT script. For example, some of the items that are not set by default
are the commit frequency. By default, the program will commit after each table.
This can be set under the Scripts tab under the Tools | Options menu. Under the
General tab, you can also specify who will appear as the author of the script.
Once you have the tables you want script ready, hit the F5 key to generate
the script. This will generate the script then show you the results in Notepad
or the AutoInserts browser (shown below).
With the script created, you can now modify the comments or data and save
again. It is immediately obvious when loading the application that LockwoodTech
has spent loads of time on the application interface and usability. It is the
easiest and sleekest application I've ever seen in the tech field. I've found
AutoInserts to be an invaluable tool to quickly do a job that would've taken me
hours previously to accomplish. I found myself using the tool so much that I finally created a shortcut on my sacred desktop to it.
Quick Pros and Cons
* Note : Many of these cons are going to be fixed in an upcoming release.
I did receive a copy of AutoInserts that I plan to continue to use.