I am utilizing a Database Project in Visual Studio 2010 to handle all my database code.

For my application, we separate the information for every client to their own copy from the database, having a common database to map customers towards the appropriate database.

Let me keep a number of different sample data takes hold TFS together with the code and have the ability to selectively deploy one of these as necessary.

I have produced scripts for 2 different client databases and also have both of them held in the publish-deployment directory in tfs. Let me maintain another that people use for demos and unit testing.

It is possible to method to make :r conditional in SQLCMD? Exist other suggestions or guidelines based on how to get this done? I have been reading through with the Visual Studio 2010 Database Projects Guidance Document, but haven't found anything yet.

Thanks ahead of time for just about any assist you to can offer.

I had been on course before adding a flexible towards the project and checking it within the script.

You will find two little catches:

  1. Within the .sqlcmdvars file the need for the variable value must range from the single quotes around it
  2. This process creates a single script with all the sample data incorporated, only the main one per the variable will get performed. Because the sample data will get more complete, this can be a pretty large file, but this ought to be suitable for my reasons. I saw how this is an offer-breaker for really large data sets. Also, the person scripts can't possess the GO separator inside them becasue it is found in a BEGIN Finish block.

Here's my file:

/* All deployments get the reference data */
:r .\ReferenceData.PostDeployment.sql

IF ('$(SampleSet)' = 'ClientA')
BEGIN
    :r .\SampleData.ClientA.PostDeployment.sql
END
ELSE IF ('$(SampleSet)' = 'ClientB')
BEGIN
    :r .\SampleData.ClientB.PostDeployment.sql
END

Credit to Jamie Thomson and this article for that cited value bit.

Might be it's not directly you would like, however for easy database creation &lifier population from .Internet review use of wide migrate tool. My preference (Migrator.Internet) but full review are available there: http://flux88.com/blog/net-database-migration-tool-roundup/