Scripting a SQL Server Database is Easy with sp_generate_inserts

One of the most useful SQL Server development tools I've stumbled upon recently is a system stored procedure that this guy created called "sp_generate_inserts".


It does precisely what you think it'd do - if you have text data to insert in-masse from a development database, the stored proc creates a data table which contains the INSERT commands you'll eventually need to run against your production database system. I like handling data this way - in my opinion, it's probably best to script changes out so that you can store that script into your usual source code repository.

The best part is that this particular stored procedure has a myriad of options, here's some pointers on usage, from the provider's web site:

Usage:
Example 1: To generate INSERT statements for table 'titles':
EXEC sp_generate_inserts 'titles'

Example 2: To ommit the column list in the INSERT statement: (Column list is included by default)
NOTE: If you have too many columns, you are advised to ommit column list, as shown below, to avoid erroneous results
EXEC sp_generate_inserts 'titles', @Include_Column_List = 0

Example 3: To generate INSERT statements for 'titlesCopy' table from 'titles' table:
EXEC sp_generate_inserts 'titles', 'titlesCopy'

Example 4: To generate INSERT statements for 'titles' table for only those titles which contain the word 'Computer' in them:
EXEC sp_generate_inserts 'titles', @From = "from titles where title like '%Computer%'"

Example 5: To specify that you want to include TIMESTAMP column's data as well in the INSERT statement:
NOTE: By default TIMESTAMP column's data is not scripted
EXEC sp_generate_inserts 'titles', @Include_Timestamp = 1

Example 6: To print the debug information:
EXEC sp_generate_inserts 'titles', @debug_mode = 1

Example 7: If you are not the owner of the table, use @owner parameter to specify the owner name:
NOTE: To use this option, you must have SELECT permissions on that table
EXEC sp_generate_inserts Nickstable, @owner = 'Nick'

Example 8: To generate INSERT statements for the rest of the columns excluding images:
NOTE: When using this otion, DO NOT set @include_column_list parameter to 0
EXEC sp_generate_inserts imgtable, @ommit_images = 1

Example 9: To generate INSERT statements for the rest of the columns excluding IDENTITY column:
EXEC sp_generate_inserts mytable, @ommit_identity = 1

Example 10: To generate INSERT statements for the top 10 rows in the table:
EXEC sp_generate_inserts mytable, @top = 10

Example 11: To generate INSERT statements only with the columns you want:
EXEC sp_generate_inserts titles, @cols_to_include = "'title','title_id','au_id'"

Example 12: To generate INSERT statements by ommitting some columns:
EXEC sp_generate_inserts titles, @cols_to_exclude = "'title','title_id','au_id'"

Example 13: To avoid checking the foreign key constraints while loading data with INSERT statements:
NOTE: The @disable_constraints option will disable foreign key constraints, by assuming that the source data is valid and referentially sound
EXEC sp_generate_inserts titles, @disable_constraints = 1

Example 14: To avoid scripting data from computed columns:
EXEC sp_generate_inserts MyTable, @ommit_computed_cols = 1

The stored procedure is treated as a system stored procedure, so it installs to your "master" database. It's then available to all of your databases on the server, but the downside is that you probably shouldn't install this on your production box (keep tight control of those systems, kids!).

When you're dealing with a database which has thousands and thousands of games listed, this stored procedure is really useful for syncing your development environment with your production environment. Less time inserting data, and more time playing the games I love!

Follow the link above to get a copy of the stored procedure for either SQL 2000 or SQL 2005.