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)

Data script generator

By Jacek Osuchowski,

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

Total article views: 1518 | Views in the last 30 days: 4
 
Related Articles
FORUM

Characters in IIF statements

Using Characters in IIF statements

FORUM

character limitations on sp name, & function name

character limitations on sp name, & function name

FORUM

Non-printable characters in SQL Scripts out of SSMS?

Using Script as Create results in scripts with non-printable characters

ARTICLE

Generating Insert Statements

This article from new author Oleg Netchaev describes the cursor-less script used to generate insert ...

SCRIPT

LongPrint

Simple script overcomes the 8000 character limitation in the T-SQL print Command

Tags
 
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