Data script generator

,

If you are working in a development environment you are probably frequently facing a task of generating test data so you can test your code. This is one of the most tedious tasks I can think of, especially if you are in a multi-user environment where each developer uses it's own local copy of the database for testing. In order to ease the pain of creating and sharing the test data I developed a simple stored procedure to generate script data for a given table.

The script will generate insert statement for each record in the table. You can store the output in a text file, load it into query window on a different computer and populate the table with data.

The script works with most SQL Server native data types. I did not test this with every data type SQL Server supports (binary, image, sql_variant, etc) so I do not know if this is going to work as is for all your tables. If there is a data type causing an issue you can probably modify the CASE clause to get what you want. This is not a Swiss army knife of data extraction but for quick and unsophisticated data scripting works fine.

Limitations:
If you are using SQL Sever 2000 please remember that Query Analyzer is going to limit the Insert statement to 256 or so characters - you may have to create a small VB or C# app to get some use of it. This is not an issue for SQL Server 2005 Management Studio.

Because the entire statement that is passed to sp_executesql is limited to 4000 characters if you have a table with long field names and/or a large number of fields you may hit that limit and the SP will not work properly. If this happens you could play with the sizes of @Insert and @Select variables to get it going but of course there is a limit to what you can correct this way.

The script is fairly simple SELECT statement so I do not think it requires explanation how it works but I think there are two features worth mentioning:
1 - it scripts NULL values
2 - it handles strings containing quote characters

CREATE PROCEDURE [dbo].[sa_ScriptTableData]
@TableName NVARCHAR(100)
AS
DECLARE @Insert VARCHAR(2000)
DECLARE @Select VARCHAR(2000)
DECLARE @Query VARCHAR(4000)
SELECT 
@Insert = ISNULL(@Insert + ',', '') + SC.name,
@Select = 
CASE 
WHEN SC.collation IS NULL AND SC.xtype NOT IN (61, 58) THEN ISNULL(@Select + ' + '','' + ', '') + 'ISNULL(CAST(' + SC.name + ' AS NVARCHAR), ' + '''NULL'')'
WHEN SC.collation IS NULL AND SC.xtype IN (61, 58) THEN ISNULL(@Select + ' + '','' + ', '') + 'ISNULL('''''''' + CONVERT(NVARCHAR, ' + SC.name + ', 121) + '''''''', ' + '''NULL'')'
ELSE ISNULL(@Select + ' + '','' + ', '') + 'ISNULL('''''''' + REPLACE(' + SC.name + ', '''''''','''''''''''') + '''''''', ''NULL'')' 
END
FROM syscolumns SC
INNER JOIN sysobjects SO ON SC.id = SO.id
AND SO.xtype = 'U'
AND SO.name = @TableName
ORDER BY SC.colID
SET @Query = 'SELECT ''INSERT INTO ' + @TableName + '(' + @Insert + ') VALUES ('' + ' + @Select + ' + '')''' + ' FROM ' + @TableName
EXEC sp_executesql @Query

Rate

3 (7)

Share

Share

Rate

3 (7)