Generate Data Insert Scripts Using Hex Strings to Preserve Data

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