Generating INSERT statements to accompany your Database Projects

Generating INSERT statements to accompany your Database Projects

Posted by richard | January 7, 2010 | SQL Server, Visual Studio 2008

Microsoft has yet to provide us with some kind of utility to handle the importing, managing, versioning, and deploying of data along with our schema changes inside Visual Studio Team System 2008 database projects. For most of the teams I work with, their needs are simple: they just want the ability to store data (INSERT statements are fine) in scripts within their database projects. Ideally the project would be smart enough to know which version of data goes with which version of schema, but for now they’re able to live with handling that manually.

Here’s one solution, albeit a manual one:

1. Create a database project.
2. Import database schema.
3. Launch SQL Server Management Studio (2008 version).
4. Right-click on the database and select Tasks > Generate Scripts.
5. Select the database and under Script Options deselect everything except for “Script Data”.

    ScriptOptions  

6. Click Next and select just the Tables you want (ideally just the smaller, static/lookup tables).

    ChooseTables

7. Click Next and specify the file to generate – something like LookupTableData.sql and let it rip.
8. You can now take that script and add it to your database project in a folder for data-related scripts.

    DatabaseProject

Ideally you would link in the INSERT script(s) to your Post-Deployment script to automatically populate the data tables upon deployment. You can also use the option in the Generate Scripts dialog to give you one file per table, to maximize your versioning options. If you are already using Data Generation Plans, be careful to not overlap what they are already doing. For more information, be sure to read Barclay Hill’s Part 1 and Part 2 of a posting on how to manage data motion during your deployments.

Related Blogs

Posted by Simon Reindl | September 19, 2012
Useful TFS Links
I have just finished delivering a Team Foundation Course today, and we had some great discussions around TFS Administration, upgrading, adoption etc. As usual, there is a whole pile of...
Posted by Simon Reindl | December 10, 2010
Top 5 Reasons to use VS Database Projects
There is a traditional resistance to using database projects – the production system is the reference point, and I have a backup, so what is the problem, right? Since the...
Posted by Simon Reindl | November 17, 2010
Tech Ed Berlin Links
On the Tech Ed Berlin VS2010 Ultimate booth, we fielded a wide range of questions about Visual Studio and TFS. We used a few web pages to help with the...