Introduction
ScriptBuilder is a handy, simple tool for generating INSERT
statements against SQL Server databases (2000-2008).
Background
Programmers and QA often have to generate scripts when migrating their applications from the development environment to QA and into production.
Situations frequently prohibit the use of SSIS or DTS.
ScriptBuilder creates portable, safe INSERT
statements for this purpose.
Using the Code
This is a complete application. Feel free to use any part of the code as you need.
The classes, OutputColumn
, ColumnManager
, and DBAccess
can be taken out and compiled into a DLL to use with your application.
Functionality
Connecting to the Server
Click on the Connect button to open the connection dialog.
Type in the server name and choose either NT Authentication or SQL server credentials.
Once connected, the databases drop down will populate. Choose the database to generate a list of tables in that database.
Choosing a table will populate the grid with a list of columns. If the column contains an identity specification, there will be a key icon next to the column name.
Setting Search Criteria
The WHERE
field is a free form text field which permits the user to enter any T-SQL statements that would appear in a WHERE
clause. Complex expressions and sub queries are permitted.
Users can drag and drop the column name from the grid into the WHERE
section.
Use of the actual WHERE
keyword is optional. If it is not provided, it will be prepended by the program.
Selecting Output
Columns are chosen by clicking on the Select column. At least one column must be checked. If an identity column is selected, an IDENTITY_INSERT
statement is added to the top and bottom of the output.
The Custom Value field allows the user to override the values in the generated SQL. There are two kinds of Custom Values: literal strings which will appear in the VALUES
statement as-is and "Run At Server" statements.
By checking the Run At Server column, the statements in the Custom Value field will be evaluated by SQL Server when the data is being fetched.
For example, the GETDATE()
function will do two very different things depending on whether Run At Server is checked or not.
If checked, the data in the script will be the actual date that the script ran. VALUES(1243, 'October 3, 2009 09:45:12',...)
If it isn't checked, the GETDATE()
statement will appear in the VALUES
statement and will insert the actual date that the script is run. Example: VALUES(1243,GETDATE(),...
Creating the Script File
Finally, select a file and click the Make Script button. When successful, a hyperlink control will appear allowing the user to view the file.
The Plumbing
Column meta data is stored in the OutputColumn
class. Each field is exposed as a read/write property.
OutputColumn::OutputColumn(void)
{
_colName = String::Empty;
_isKey = false;
_colOverride = String::Empty;
_colType = String::Empty;
}
Columns collections are built by the user by selecting which output fields they want on the form. Each row is passed into the ColumnManager
object through the addColumn
method:
OutputColumn^ col = gcnew OutputColumn;
col->ColumnName = row["name"]->ToString();
col->ColumnOverride = colOverride;
col->ColumnType = row["data_type"]->ToString();
col->IsKey = Convert::ToBoolean(row["id_col"]);
col->RunAtServer = runAtServer;
_colList->Add(col);
Generating the code is accomplished by dynamically generating two SQL statements: the SQL that fetches the data to be scripted and the actual outputted SQL script.
The snippet below generates two column lists: the columns that appear in the INSERT INTO
(columns) and a list of both columns and "Run At Server" T-SQL statements.
For example, a "Run As Server" Custom statement might be LEFT(column_one, 3)
or GETDATE()
if you want to capture the date the script was generated.
...
for (int i = 0; i < _colList->Count; i++)
{
sbHeader->Append("[" + _colList[i]->ColumnName + "]" +
(i < _colList->Count -1 ? "," : ""));
if(_colList[i]->RunAtServer && _colList[i]->ColumnOverride != String::Empty)
{
sbSelCols->Append(_colList[i]->ColumnOverride +
(i < _colList->Count -1 ? "," : ""));
}
else
{
sbSelCols->Append("[" + _colList[i]->ColumnName + "]" +
(i < _colList->Count -1 ? "," : ""));
}
...
The data fetch is a simple dynamic SQL statement which can include an optional WHERE
clause.
SqlDataReader^ rdr = _dba->getData("SELECT " + selCols + " FROM " + tableName + " " +
whereClause);
while(rdr->Read())
{
sbGetData->Append(insertHeader);
sbGetData->Append("VALUES(");
...
The application spins through the returned SQLDataReader
and completes the VALUES
(data values) statements. Custom overrides that are not set to "Run at Server" simply overlay the returned value. This is useful if you want the created date column to populate with the current date time. You would do this with the GETDATE()
function. Maybe you want to overwrite the original last update user with something like 'migration_user'
:
for(int i = 0; i < rdr->FieldCount; i++)
{
if(_colList[i]->ColumnOverride->Equals(String::Empty) ||
_colList[i]->RunAtServer)
{
if(rdr[i]->GetType() == String::typeid ||
rdr[i]->GetType() == DateTime::typeid)
{
fieldData = rdr[i]->ToString();
fieldData = escapeText(fieldData);
sbGetData->Append("'" + fieldData + "'" +
(i < _colList->Count -1 ? "," : ""));
}
else if (rdr[i]->GetType() == DBNull::typeid)
{
sbGetData->Append("NULL" + (i < _colList->Count -1 ? "," : ""));
}
else if (rdr[i]->GetType() == Boolean::typeid)
{
sbGetData->Append(Convert::ToInt32(rdr->GetBoolean(i)).ToString() +
(i < _colList->Count -1 ? "," : ""));
}
else
{
fieldData = rdr[i]->ToString();
fieldData = escapeText(fieldData);
sbGetData->Append(fieldData +
(i < _colList->Count -1 ? "," : ""));
}
}
else {
sbGetData->Append(_colList[i]->ColumnOverride +
(i < _colList->Count -1 ? "," : ""));
}
}
...
The resulting string
is written to a file specified by the user with a .sql extension.
Points of Interest
Cool features include:
- The ability to write custom expressions that will override values in the table.
- "Run At Server" custom expressions that will evaluate T-SQL statements while generating data.
- Automatic generation of
IDENTITY_INSERT
statements if an identity column is found.
Other fancy stuff is the ability to drag and drop field names from the column grid into the WHERE
clause text box and the Custom column which allows you to override any column value with a literal string or any T-SQL function (e.g. GETDATE()
).
Be careful with large datasets, it can get very slow.
History
- 13th October, 2009: Initial post
- 7th December, 2009: Updated source files - small bug fixes