Create INSERT batch files easily with ScriptBuilder

• Download source - 74.52 KB

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.

...

//Create comma separated list of column names
for (int i = 0; i < _colList->Count; i++) 
{
     sbHeader->Append("[" + _colList[i]->ColumnName + "]" + 
	(i < _colList->Count -1 ? "," : ""));

//Build Select string for data call. User can override column name with 
//a T-SQL statement (or string literal).
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.

//Fetch data to be scripted
SqlDataReader^ rdr = _dba->getData("SELECT " + selCols + " FROM " + tableName + " " +
    whereClause);
		
//Build INSERT statement rows
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 there is no column override or there is one but it's set to RunAtServer
    //put in the value returned in the DataTable
    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();
		//check for escaping quotes
		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();
	    //check for escaping quotes
	    fieldData = escapeText(fieldData);

	    sbGetData->Append(fieldData + 
		(i < _colList->Count -1 ? "," : ""));
	}

     }	
     else //Using a column override value
     {
	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

• 13^th October, 2009: Initial post
• 7^th December, 2009: Updated source files - small bug fixes