Sunday, October 5, 2008

Generating Insert Scripts to Move Static Data Between Environments

You may create a codes table that defines an order type or a sales code, ect. You probably create the table in your local database and manually enter in the initial rows. When it is time to move to test, stage, and production, you don't want to manually key in the data. You have a couple options. You can write an SSIS job, or do an export to file and re-import the data in the new environment. This just seemed like more work that necessary. I thought it would be really nice if I could have them documented as insert statements, so the installer can simply run it when it is time to go to production.

To handle this scenario, I found an excellent post from Narayana Vyas Kondreddi. All you need to do is install his stored procedure in your master database and then you can generate insert statements by calling that stored procedure in the database or your choosing.

Here are the two main type of execute statements that I found useful.

  • Generating inserts for a table where you want all columns scripted:
EXEC sp_generate_inserts 'titles'

  • Generating a table to include all columns except for the identity column:
EXEC sp_generate_inserts mytable, @ommit_identity = 1

Thanks Narayana for a great post!!!

No comments: