Pages

Monday, July 20, 2009

Script all data of a table

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
/******************************************************************

Author
======
Parag Shukla

Summary
=======
Script to create a SELECT statement to script all data of a specified table

Parameters
==========

@table_name
The name of the table to be scripted

@handle_big_binary
If set to 1 the user defined function udf_varbintohexstr_big will be used
to convert BINARY, VARBINARY and IMAGE data. For futher information see remarks.

@column_names
If set to 0 only the values to be inserted will be scripted; the column names wont.
This saves memory but the destination tables needs exactly the same columns in
same order.
If set to 1 also the names of the columns to insert the values into will be scripted.

******************************************************************/

SET NOCOUNT ON

DECLARE @table_name SYSNAME
DECLARE @handle_big_binary BIT
DECLARE @column_names BIT

-- ////////////////////
-- -> Configuration
SET @table_name = 'dbo.Categories'
SET @handle_big_binary = 1
SET @column_names = 1
-- <- Configuration
-- ////////////////////

DECLARE @object_id INT
DECLARE @schema_id INT

--SELECT * FROM sys.all_objects
SELECT @object_id = object_id, @schema_id = schema_id
FROM sys.tables
WHERE object_id = OBJECT_ID(@table_name)


DECLARE @columns TABLE (column_name SYSNAME, ordinal_position INT, data_type SYSNAME, data_length INT, is_nullable BIT)

-- Get all column information
INSERT INTO @columns
SELECT column_name, ordinal_position, data_type, character_maximum_length, CASE WHEN is_nullable = 'YES' THEN 1 ELSE 0 END
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = SCHEMA_NAME(@schema_id)
AND TABLE_NAME = OBJECT_NAME(@object_id)

DECLARE @select VARCHAR(MAX)
DECLARE @insert VARCHAR(MAX)
DECLARE @crlf CHAR(2)
DECLARE @sql VARCHAR(MAX)
DECLARE @first BIT
DECLARE @pos INT
SET @pos = 1

SET @crlf = CHAR(13) + CHAR(10)

WHILE EXISTS (SELECT TOP 1 * FROM @columns WHERE ordinal_position >= @pos)
BEGIN
DECLARE @column_name SYSNAME
DECLARE @data_type SYSNAME
DECLARE @data_length INT
DECLARE @is_nullable BIT

-- Get information for the current column
SELECT @column_name = column_name, @data_type = data_type, @data_length = data_length, @is_nullable = is_nullable
FROM @columns
WHERE ordinal_position = @pos

-- Create column select information to script the name of the source/destination column if configured
IF (@select IS NULL)
SET @select = ' ''' + QUOTENAME(@column_name)
ELSE
SET @select = @select + ','' + ' + @crlf + ' ''' + QUOTENAME(@column_name)

-- Handle NULL values
SET @sql = ' '
SET @sql = @sql + 'CASE WHEN ' + QUOTENAME(@column_name) + ' IS NULL THEN ''NULL'' ELSE '

-- Handle the different data types
IF (@data_type IN ('bigint', 'bit', 'decimal', 'float', 'int', 'money', 'numeric',
'real', 'smallint', 'smallmoney', 'tinyint'))
BEGIN
SET @sql = @sql + 'CONVERT(VARCHAR(40), ' + QUOTENAME(@column_name) + ')'
END
ELSE IF (@data_type IN ('char', 'nchar', 'nvarchar', 'varchar'))
BEGIN
SET @sql = @sql + ''''''''' + REPLACE(' + QUOTENAME(@column_name) + ', '''''''', '''''''''''') + '''''''''
END
ELSE IF (@data_type = 'date')
BEGIN
SET @sql = @sql + '''CONVERT(DATE, '' + master.sys.fn_varbintohexstr (CONVERT(BINARY(3), ' + QUOTENAME(@column_name) + ')) + '')'''
END
ELSE IF (@data_type = 'time')
BEGIN
SET @sql = @sql + '''CONVERT(TIME, '' + master.sys.fn_varbintohexstr (CONVERT(BINARY(5), ' + QUOTENAME(@column_name) + ')) + '')'''
END
ELSE IF (@data_type = 'datetime')
BEGIN
SET @sql = @sql + '''CONVERT(DATETIME, '' + master.sys.fn_varbintohexstr (CONVERT(BINARY(8), ' + QUOTENAME(@column_name) + ')) + '')'''
END
ELSE IF (@data_type = 'datetime2')
BEGIN
SET @sql = @sql + '''CONVERT(DATETIME2, '' + master.sys.fn_varbintohexstr (CONVERT(BINARY(8), ' + QUOTENAME(@column_name) + ')) + '')'''
END
ELSE IF (@data_type = 'smalldatetime')
BEGIN
SET @sql = @sql + '''CONVERT(SMALLDATETIME, '' + master.sys.fn_varbintohexstr (CONVERT(BINARY(4), ' + QUOTENAME(@column_name) + ')) + '')'''
END
ELSE IF (@data_type = 'text')
BEGIN
SET @sql = @sql + ''''''''' + REPLACE(CONVERT(VARCHAR(MAX), ' + QUOTENAME(@column_name) + '), '''''''', '''''''''''') + '''''''''
END
ELSE IF (@data_type IN ('ntext', 'xml'))
BEGIN
SET @sql = @sql + ''''''''' + REPLACE(CONVERT(NVARCHAR(MAX), ' + QUOTENAME(@column_name) + '), '''''''', '''''''''''') + '''''''''
END
ELSE IF (@data_type IN ('binary', 'varbinary'))
BEGIN
-- Use udf_varbintohexstr_big if available to avoid cutted binary data
IF (@handle_big_binary = 1)
SET @sql = @sql + ' dbo.udf_varbintohexstr_big (' + QUOTENAME(@column_name) + ')'
ELSE
SET @sql = @sql + ' master.sys.fn_varbintohexstr (' + QUOTENAME(@column_name) + ')'
END
ELSE IF (@data_type = 'timestamp')
BEGIN
SET @sql = @sql + '''CONVERT(TIMESTAMP, '' + master.sys.fn_varbintohexstr (CONVERT(BINARY(8), ' + QUOTENAME(@column_name) + ')) + '')'''
END
ELSE IF (@data_type = 'uniqueidentifier')
BEGIN
SET @sql = @sql + '''CONVERT(UNIQUEIDENTIFIER, '' + master.sys.fn_varbintohexstr (CONVERT(BINARY(16), ' + QUOTENAME(@column_name) + ')) + '')'''
END
ELSE IF (@data_type = 'image')
BEGIN
-- Use udf_varbintohexstr_big if available to avoid cutted binary data
IF (@handle_big_binary = 1)
SET @sql = @sql + ' dbo.udf_varbintohexstr_big (CONVERT(VARBINARY(MAX), ' + QUOTENAME(@column_name) + '))'
ELSE
SET @sql = @sql + ' master.sys.fn_varbintohexstr (CONVERT(VARBINARY(MAX), ' + QUOTENAME(@column_name) + '))'
END
ELSE
BEGIN
PRINT 'ERROR: Not supported data type: ' + @data_type
RETURN
END

SET @sql = @sql + ' END'

-- Script line end for finish or next column
IF EXISTS (SELECT TOP 1 * FROM @columns WHERE ordinal_position > @pos)
SET @sql = @sql + ' + '', '' +'
ELSE
SET @sql = @sql + ' + '

-- Remember the data script
IF (@insert IS NULL)
SET @insert = @sql
ELSE
SET @insert = @insert + @crlf + @sql

SET @pos = @pos + 1
END

-- Close the column names select
SET @select = @select + ''' +'

-- Print the INSERT INTO part
PRINT 'SELECT ''INSERT INTO ' + @table_name + ''' + '

-- Print the column names if configured
IF (@column_names = 1)
BEGIN
PRINT ' ''('' + '
PRINT @select
PRINT ' '')'' + '
END

PRINT ' ''VALUES ('' +'

-- Print the data scripting
PRINT @insert

-- Script the end of the statement
PRINT ' '')'''
PRINT ' FROM ' + @table_name
Posted by Dr. Parag Shukla at 10:46 PM 0 comments

Thursday, July 2, 2009

FOR XML PATH - How to generate a Delimited String using FOR XML PATH?

There are two common string operations where I used to write a TSQL loop in the SQL server 2000 era.

  1. To split a delimited string and return a set
  2. To generate a delimited string from a set

The XML enhancements added to SQL Server 2005 made both these operations easier with XML. I think, most of the times these operations are done in small pieces of data. Though you can do these operations on extremely large data, I don't think it is advisable. There are other ways to handle large chunks of data.

In this post, lets see how we could generate a delimited string using FOR XML PATH.

Let us first see the source data.

DECLARE @companies Table( 
 CompanyID INT, 
 CompanyCode  int
)

insert into @companies(CompanyID, CompanyCode) values(1,1)
insert into @companies(CompanyID, CompanyCode) values(1,2)
insert into @companies(CompanyID, CompanyCode) values(2,1)
insert into @companies(CompanyID, CompanyCode) values(2,2)
insert into @companies(CompanyID, CompanyCode) values(2,3)
insert into @companies(CompanyID, CompanyCode) values(2,4)
insert into @companies(CompanyID, CompanyCode) values(3,1)
insert into @companies(CompanyID, CompanyCode) values(3,2)

SELECT * FROM @companies
/*
CompanyID   CompanyCode
----------- -----------
1           1
1           2
2           1
2           2
2           3
2           4
3           1
3           2
*/

This is the result that we need. 

/*
CompanyID   CompanyString
----------- -------------------------
1           1,2
2           1,2,3,4
3           1,2
*/

One option is to run a loop that constructs a delimited string for each CompanyID. Another option is to create a function that returns a delimited string for each company ID. I am presenting a third option using FOR XML PATH.

SELECT CompanyID,
 REPLACE((SELECT
     CompanyCode AS 'data()'
  FROM @companies c2
  WHERE c2.CompanyID = c1.CompanyID
  FOR XML PATH('')), ' ', ',') AS CompanyString
FROM @companies c1
GROUP BY CompanyID

/*
CompanyID   CompanyString
----------- -------------------------
1           1,2
2           1,2,3,4
3           1,2

The above query uses FOR XML PATH to return a comma delimited string containing the company
code of each row.
*/
Posted by Dr. Parag Shukla at 11:24 PM 1 comments
Subscribe to: Posts (Atom)