Have you ever found yourself needing to transfer small amounts of data from one database to another? Maybe you need to restore a single record that was accidentally deleted, or update a coding table with new additions. While there are tools available for more complex data transfers, sometimes you just need a quick and easy solution for those ad hoc tasks.

In this article, we will explore a simple script that can help you with these smaller data transfers. The script uses SQL Server’s system tables, a cursor, and dynamic SQL to generate INSERT statements for the specified table. Let’s take a closer look at how it works.

The spScriptInsert Procedure

The heart of the script is the stored procedure called spScriptInsert. This procedure takes two parameters: the name of the table you want to transfer data from, and an optional condition to filter the data. If no condition is provided, all records from the table will be included.

Inside the procedure, the script first assembles the field list for the table into a temporary table called “#fields”. It retrieves the column order, name, and type from the system tables and stores them in this temporary table. The script then uses a cursor to iterate through the fields and construct the field list and values list for the INSERT statements.

For each field, the script checks the data type. If the type is “money”, a special case function called spMVal is used to format the value. Otherwise, a general function called spVal is used to format the value. These functions handle cases where the value is NULL or contains special characters.

Once the field list and values list are constructed, the script builds a SELECT statement that returns the INSERT statements for each record in the target table, based on the provided condition. The SELECT statement is executed using dynamic SQL, and the results can be copied and pasted into another query window in SQL Management Studio or Query Analyzer connected to the target database.

Usage Example

Let’s say we have a table called “Products” in the “Northwind” database, and we want to transfer a subset of records based on a condition. We can use the spScriptInsert procedure like this:

spScriptInsert 'Products', 'ProductID BETWEEN 70 AND 73'

This will generate INSERT statements for the records with ProductID between 70 and 73 in the Products table. You can then copy the generated INSERT statements and execute them in the target database to transfer the data.

Limitations and Improvements

While this script can be handy for small data transfers, there are a few limitations and possibilities for improvement to keep in mind:

• If the table has a large number of fields, the generated SQL may exceed the maximum length of the variable used to store it. To avoid this, the script uses short names for the helper functions.
• If the table has an IDENTITY field, you need to enable inserts for it in the target database before executing the generated INSERT statements.
• The script does not handle “text” fields. If your table has such fields, you will need to modify the script accordingly.
• If the table has a lot of fields, the variables used in the spScriptInsert procedure may overflow, resulting in a cryptic error message. It would be possible to improve error handling and provide more user-friendly error messages.
• The script uses the “old” system tables, so it is compatible with both SQL Server 2000 and 2005.

In summary, while this script may not be suitable for production updates or complex data transfers, it can be a useful tool for quick and easy ad hoc data transfers or copying data between test environments. It can also come in handy when you need to restore a deleted record from a backup or log-shipped copy of a database.

Give it a try and see if it simplifies your data transfer tasks!