Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Script all data of a table

By Florian Reischl,

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

Total article views: 20898 | Views in the last 30 days: 13
 
Related Articles
ARTICLE

Exploring the Varbinary Type

A brief look at the Varbinary data type and its uses in SQL Server for beginners.

FORUM

URGENT: Error executing scripts......

URGENT: Error executing scripts......

FORUM

Error in creating a T-Script (fn_diagramobjects has the execution context ExecuteAsUser. ExecuteAsUser is not supported)

(fn_diagramobjects has the execution context ExecuteAsUser. ExecuteAsUser is not supported)

FORUM

Connditional executing of script

Connditional executing of script

Tags
script data    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones