I've got a Database project which works fine for my local MSSQL 2008 database.
It has a script under scripts/post-deployment that inserts standing data configuration into a settings table, and other tables. I've got 1 file for each table, e.g. a Setting.sql file to insert data into the Settings table.
The settings will be different depending on the database I deploy to.
How can I script this? Basicaly I would like to be able to have say 2 files,
Prod.Setting.sql and Dev.Setting.sql and VS 2010 would use the appropriate script depending on what database (environment) I am deploying to.
Completely doable but there two options and a few steps involved. You can have a complete duplicate set of scripts, one for each configuration. Or, you can have one set of scripts, the contents of which take into account the configuration you are using. I'll go with the first, and I'll keep it simple.
Create two solution configurations, or use Debug and Release if you like. I'll use these for the example.
For each configuration, create a new .sqlcmdvars file.
Database_Release.sqlcmdvars
Database_Debug.sqlcmdvars
Switch your solution configuration to each and in the database project properties change the variables file drop down to point at the corresponding file you created.
In each of those files you can define variables to be used during deployment. Create a new variable in each one
$(DeploymentConfiguration)
And set its value in each one to either Debug or Release
Then in any of your Pre or Post deployment scripts you can do something like so:
IF '$(DeploymentConfiguration)' = 'Debug'
BEGIN
PRINT 'Executing Debug deployment'
:r .\Debug\SomeNeededScript.sql
END
IF '$(DeploymentConfiguration)' = 'Release'
BEGIN
PRINT 'Executing Release deployment'
:r .\Release\Anotherscript.sql
END
