Data Archiving - Generate Data Insert Scripts Using Hex Strings to Preserve Data

Applicability:

                 SQL Server 2000:        Not Supported**
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Not Tested        

Credits:

Author:    Unknown
Date:        28 Nov 2011

Description

There are several well publicised methods of generating data insert scripts for SQL Server tables and with good reason - until SQL 2008, this feature was not available through any of the Microsoft SQL Server management toolset.  SQL 2008 does provide this function, as does SQL 2008 R2 (although Microsoft decided to move its location giving the initial impression that it had disappeared)
 
However, these methods all script text using the standard unicode text string  i.e. N'This is text'.
 
There will always be some user (or 3rd party software vendor) who will ruin your day by including control characters or other non-text characters in the text data.  Fun, fun, fun.
 
This script adopts a new approach by converting all characters in a text column into one Hex string.
These will be processed by a standard insert statement - you just have to look as some of the SQL Server Replication stored procedures to find examples of this (one of the common SQL Injection attacks is even delivered using strings in this format in attempt to mask the malicious intent of the string)
 
The code is not particularly performant for larger data volumes, but then this kind of task is normally required when setting up or transferring lookup table data between servers/databases.  For larger data volumes, I would recommend using BCP in native format mode.
 
**NOTE:  This code could be adapted for SQL 2000 by replacing the VARCHAR(MAX) variables with VARCHAR(8000), but the verbose nature of a hex string (just over 4 * the size) will limit the size of the text strings that can be processed

Code

 

DROP PROCEDURE  dbo.usp_generate_inserts
GO

CREATE PROCEDURE dbo.usp_generate_inserts
(
    
@Table NVARCHAR(255)
)
AS
BEGIN
    SET NOCOUNT ON
    
    DECLARE
      
@Is_Identity        BIT;  
      
@Columns            NVARCHAR(MAX);
      
@Values             NVARCHAR(MAX);
      
@Script             NVARCHAR(MAX);
        
    
IF ISNULL(CHARINDEX('.', @Table), 0) = 0
    
BEGIN
        PRINT
'Procedure dbo.usp_generate_inserts expects a table_name parameter in the form of schema_name.table_name';
    
END
    
    ELSE
    
    BEGIN
        
-- initialize variables as otherwise the padding will fail (return nulls for nvarchar(max) types)
        
SET @Is_Identity = 0;
        
SET @Columns = '';
        
SET @Values = '';
        
SET @Script = '';
        
/*
            The following select makes an assumption that the identity column should be included in
            the insert statements. Such inserts still work when coupled with identity_insert toggle,  
            which is typically used when there is a need to "plug the holes" in the identity values.
            Please note the special handling of the text data type. The type should never be present
            in SQL Server 2005 tables because it will not be supported in future versions, but there
            are unfortunately plenty of tables with text columns out there, patiently waiting for  
            someone to upgrade them to varchar(max).
        */
        
SELECT
            
@Is_Identity = @Is_Identity | COLUMNPROPERTY(OBJECT_ID(@Table), column_name, 'IsIdentity'),
            
@Columns = @Columns + ', ' + '['+ column_name + ']',
            
@Values =
                
@Values + ' + '', '' + isnull(master.dbo.fn_varbintohexstr(cast(' +
                
CASE data_type  
                    
WHEN 'text' THEN 'cast([' + column_name + '] as varchar(max))'
                    
WHEN 'ntext' THEN 'cast([' + column_name + '] as nvarchar(max))'
                    
ELSE '[' + column_name + ']'
                
END + ' as varbinary(max))), ''null'')'
            
FROM
                
information_schema.columns  
            
WHERE table_name = SUBSTRING(@Table, CHARINDEX('.', @Table) + 1, LEN(@Table))
            AND
data_type != 'timestamp'
            
ORDER BY ordinal_position;
            
        
SET @Script =
            
'select ''insert into ' + @Table + ' (' + SUBSTRING(@Columns, 3, LEN(@Columns)) +
            
') values ('' + ' + SUBSTRING(@Values, 11, LEN(@Values)) + ' + '');'' from ' + @Table + ';';
        
IF @Is_Identity = 1  
            
PRINT ('set identity_insert ' + @Table + ' on');
        
/*  
            generate insert statements. If the results to text option is set and the query results are
            completely fit then the prints are a part of the batch, but if the results to grid is set
            then the prints (identity insert related) can be gathered from the messages window.
        */
        
EXEC sp_executesql @Script;
        
        
IF @Is_Identity = 1  
            
PRINT ('set identity_insert ' + @Table + ' off');
    
END
    SET NOCOUNT OFF
END
GO


Comments