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