Script all data of a table

A script to script all data of a table. The gains of this script are:

• The performance
• Support for VARBINARY and IMAGE columns
• Almost every standard data-type is convered
• Flexibility to specify which data shall be scripted
• Security for SQL Injection

Usage

Open the script on the database to script data from.

• Configure the name of the table to be scripted (@table_name)
• Configure if the udf_varbintohexstr_big is available (only needed for IMAGE and VARBINARY data with more than 3998 bytes)
• Configure if the column names shall be scripted for destination database. This brings more flexibility because the destination table has more columns than the source table or the column order is different. Do not script the column names to save space.
• Execute the script once.
• Take the execution result as statement to script your data (maybe change something withi)
• Execute the result from first execution again
• Take the scripted data to insert them on another database/server.

Performance

Due to the fact that this script works without any cursors for data collection it is by design much faster than many other.

DATETIME, DATE and TIME columns will not be scripted as strings but as binary data which are much faster handled by SQL Server.

Support for VARBINARY and IMAGE

Most scripts to export data into SQL do not support data from type VARBINARY and IMAGE or cut them. Data with less than 3998 bytes can be scripted without any other requirements. If you want to script really huge binary data just install the plain TSQL user defined function udf_varbintohexstr_big which is also available here. Now you can script any size of binary data.

Data-Types

Supported data-types are:

• BIGINT
• BINARY
• BIT
• CHAR
• DATE
• DATETIME
• DATETIME2
• DECIMAL
• FLOAT
• IMAGE
• INT
• MONEY
• NCHAR
• NUMERIC
• NTEXT
• NVARCHAR
• REAL
• SMALLDATETIME
• SMALLINT
• SMALLMONEY
• TEXT
• TIME
• TIMESTAMP
• TINYINT
• UNIQUEIDENTIFYER
• VARBINARY
• VARCHAR
• XML

Flexibility

Because the first step of the script just creates another script which will script the data you are able to specify any WHERE/JOIN criteria for the data to be scripted if not the complete table needs to be.

It is also possible to remove specific columns from the generated script.

Security

All character data will be masked to avoid SQL injection. All column names will be quoted to ensure a valid SQL output.

Remarks

To be able to script huge VARBINARY or IMAGE data you need the udf_varbintohexstr_big which is published here. The function is plain TSQL.

Issues

Here the known issues of the script.

udf_varbintohexstr_big

Sometimes a gain becomes an issue... It is only possible to script large IMAGE data with the udf_varbintohexstr_big so need the rights to install a user defined function on the source server. The destination server does not need this function.

SSMS restrictions

The SQL Server Management Studio (SSMS) restricts the output for each cell to 40kb. The script can script any size of data but the data may be cutted by SSMS. To script any size of data just use a small program, perl/php/ps1 script to execute the script and write directly into a file.

Not supported data-types

Currently not supported data-types are:

• HIERARCHYID
• SQL_VARIANT
• User defined types