You are here:
Microsoft SQL 2008 Database Server Microsoft SQL 2008 Database Server

sp_generate_inserts - A handy SQL stored procedure for generating insert statements

When working in a development environment, there will almost certainly be a requirement to script and insert standing SQL data from your development database into your live database environment. This can often be a tedious task, until I recently discovered a handy Microsoft SQL stored procedure called sp_generate_inserts.

The stored procedure (proc) has been created by Narayana Vyas Kondreddi's and a copy of the script can be found at

Once you have added this stored procedure to your database environment, producing insert statements is a breeze.


EXEC sp_generate_inserts [tblXXX]

This would produce a scripted SQL insert statement for all of the data existing in tblXXX. The generated SQL insert, would look something like this ...

INSERT INTO [tblXXX] (id, col1, col2) VALUES (1,'val1',val2')
INSERT INTO [tblXXX] (id, col1, col2) VALUES (2,'val3',val4')
INSERT INTO [tblXXX] (id, col1, col2) VALUES (3,'val5',val6')
etc .......

One little gotcha. If your table has an identity column, you will need to switch IDENTITY INSERT [tblXXX] ON. Code for this would be ...


Insert records


It's a handy procedure to have in your SQL toolbox. Thanks to Narayana Vyas Kondreddi for the code.