SQL Stored Procedure To Generate INSERT Statements For Any Table In A Database

Last night, I was preparing the SQL scripts to deploy my database project to a new server. I had no problem generating scripts for creating database and tables but I needed help on how to generate script to insert initial records on a maintenance table. I remember that I have used a script before that create a stored procedure that can generate INSERT statements for tables in a database. After minutes of research, I found the "sp_generate_inserts" script. To help other SQL administrators and developers, I will post the download link of the script here. Just open the script in Query Analyzer, then run it, the script will create a stored procedure with name of "sp_generate_inserts" under "master" database.

Here some samples on how you can use the script. On the Query Analyzer, run the following commands:

1. To generate INSERT statements for a particular table:
EXEC sp_generate_inserts ''

2. To exclude some columns in the INSERT statement:
EXEC sp_generate_inserts '', @cols_to_exclude = "'','',''"

3. To generate INSERT statements for a table from another table:
EXEC sp_generate_inserts '', ''

4. To generate INSERT statements for specific record in a table:
EXEC sp_generate_inserts '', @From = "from where like '%sample%'"

5. To generate INSERT statements only with the columns you need:
EXEC sp_generate_inserts '', @cols_to_include = "'','',''"

Download sp_generate_inserts script

To stay up-to-date on SQL Tips & Tricks, subscribe now.