SQL Scripter 2.0

Sometimes it’s nice to have a simple tool that fills a basic need; SQL Scripter 2.0 is such a tool. SQL Scripter is a data packaging tool that lets you create T-SQL scripts to run on target systems for exporting data. This software is especially useful for DBAs who don’t have access to high-end tools such as Visual Studio Team Edition for Database Professionals or SQL Server Integration Services (SSIS).

SQL Scripter lets you automatically transport and import table data. To script data updates as T-SQL statements, SQL Scripter generates a T-SQL file that contains both an insert statement and the data associated with a row in a given table. You can then use this file to import a complete data table or to update an existing data table, while simultaneously adding new entries.

The primary features in SQL Scripter 2.0 are the ability to generate T-SQL data scripts, export data to text files, create and export reports, and leverage SQL Server Reporting Services (SSRS). Web Figure 1 shows these options on SQL Scripter’s default start screen.

Generating T-SQL Data Scripts

SQL Scripter 2.0 uses T-SQL scripts to automatically transfer data from an updated database to remote clients. As Web Figure 1 shows, SQL Scripter offers three methods for exporting data into a T-SQL file to run on a remote SQL Server database: Quick Scripter, SQL Scripter, and Script From Template.

Quick Scripter. This option lets you create scripts for data in selected tables in just one step. A single screen, which Web Figure 2 shows, lets you define your database connection, specify the type of script you would like to create (e.g., one that automatically inserts a set of values or one that creates insert statements that are wrapped by checks for a duplicate value in the same table), select the tables and columns to automatically update as part of the script(s), and define the folder location where the files will be stored. You can also specify whether to generate separate files for each table or to combine all of the selected data updates into a single file.

SQL Scripter. This tool lets you define the same elements as in Quick Scripter. However, unlike Quick Scripter’s single screen, SQL Scripter takes you through a series of screens. SQL Scripter presents the options in ordered steps, which helps reduce the process’ overall complexity.

Script From Template. This third option lets you build and record a list of tables as a template that stores your database location and target folder; you can later use this template to export data. Script From Template is especially useful for ensuring that tables are automatically updated on successive application releases.

Limitations

A limitation of SQL Scripter 2.0 is the Export Data to Text/CSV option, which lets you export table data to text or a comma-separated value (CSV) file. Although the tool lets you save a connection string as a “favorite” to reuse, when you enter a connection string you don’t get a list of the available databases or folders on your selected server. You must enter the database name or folder from scratch to create a connection.

Another weakness of SQL Scripter is that the tool was created without a set of libraries or a commandline executable option. Although the program works well interactively, it would benefit from command-line features and from the ability to leverage DLLs within a custom application or as part of an automated build process.

Recommendations
SQL Scripter is a simple, low-cost tool for quickly exporting data. If you work in a small development shop and need a way to package and transport table data without having to use a high-end tool such as Visual Studio Team Edition for Database Professionalsor SSIS, check it out.